Practical commands that you need all the time:
With the SQL Server Management Studio you can easily view all references to a stored procedure.
With PostgreSql / PGAdmin I haven't found anything like that yet.
But the following SQL command usually helps (... attention, does NOT work in all cases).
SELECT proname, proargnames, prosrc FROM pg_proc WHERE prosrc ILIKE '%{name of my function%';
Get PostgreSql version:
If you don't remember which version of PostgreSQL you're running, you can always find out with the following SQL statement:
select version();
Get full privileges to group/user
GRANT CONNECT ON DATABASE mydatabase TO myuser;
GRANT USAGE ON SCHEMA public TO myuser;
ALTER ROLE "myuser" WITH LOGIN;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO myuser;
Get clock_timestamp as string
Select to_char(clock_timestamp(), 'yyyyMMddHH24MIss')
Remember: There is a big difference between clock_timestamp() and now(). now() does not change during the execution of a single function!
Split a value by a specific character and return all values
Select unnest(string_to_array('a;b;c', ';'));
Response:
a
b
c
Temporary table
DROP TABLE IF EXISTS __addFiles_rq;
CREATE TEMP TABLE __addFiles_rq ( rid bigint, propname character varying );
Delete data in table recursively
with recursive cte(rid, parent_rid) as (
select rid, parent_rid from __rid_list where branch_role = 0
union all
select t.rid, t.parent_rid from __rid_list t inner join __rid_list c on c.rid = t.parent_rid
)
delete from __rid_list where rid in (select rid from cte)
Create INSERT comands from existing data from a table
select 'INSERT INTO my_table (lngkey, propname, legalformkey) values ('''||lngkey||''', '''||propname||''', '''||legalformkey||''')' from my_table;
Export data from a tabel
COPY (SELECT * FROM my_table) TO 'c:/temp/test.csv';
Dynamic order by
Select *
FROM public.cons_pda
order by (case when __sort_field = 'PDAKEY' and __sort_direction = 'DESC' then pdakey end) DESC,
(case when __sort_field = 'PDAKEY' and __sort_direction = 'ASC' then pdakey end) ASC,
(case when __sort_field = 'CREATEDATE' and __sort_direction = 'DESC' then data_created end) DESC,
(case when __sort_field = 'CREATEDATE' and __sort_direction = 'ASC' then data_created end) ASC;