pgSQL tips-and-tricks

anonymous code blocks for ad-hoc queries.sql

-- use EXECUTE .. USING for dynamic SQL
-- NB: DbVis needs --/ and / around do-block
do $$
begin
execute 'drop table if exists tmp';
execute 
  'create temporary table tmp as
  	select * from vektis_agb.fagbx20_s01
	where zorgverlenersnummer = $1'
	using '000004';
end
$$;

select * from tmp;

-- similar, for selecting all columns excluding only a few columns excluded
--/
do $$
declare 
    sch varchar := 'vektis_agb';
    tbl varchar := 'fagbx20_s01';
    stmt varchar; 
begin
stmt := (
    select 'select ' || array_to_string(array(
        select 'o' || '.' || c.column_name
        from information_schema.columns as c
        where table_schema = sch 
            and table_name = tbl 
            and  c.column_name != 'uitlevering'
    ), ', ') || ' from ' || sch || '.' || tbl || ' as o'
    );
execute 'drop table if exists tmp';
execute
  'create temporary table tmp as '
  || stmt;
end
$$
/

select * from tmp

creating_functions.md

CREATE TABLE AS is functionally similar to SELECT INTO. CREATE TABLE AS is the recommended syntax, since this form of SELECT INTO is not available in ECPG or PL/pgSQL, because they interpret the INTO clause differently. Furthermore, CREATE TABLE AS offers a superset of the functionality provided by SELECT INTO

CREATE OR REPLACE FUNCTION whatever()
RETURNS void AS $$
BEGIN
    create temp table as
    SELECT *
    FROM orig_table;
END; $$ LANGUAGE plpgsql;

kill process

select pg_terminate_backend(18350) from pg_stat_activity where datname='cbi';

pgsql_hash.md

In Postgres, using table name in SELECT is permitted and it has type ROW. If you cast this to type TEXT, it gives all columns concatenated together in string that is actually JSON.

Having this, you can get md5 of all columns as follows:

SELECT md5(mytable::TEXT)
FROM mytable

If you want to only use some columns, use ROW constructor and cast it to TEXT:

SELECT md5(ROW(col1, col2, col3)::TEXT)
FROM mytable

Another nice property about this solution is that md5 will be different for NULL vs. empty string.

Obligatory SQLFiddle.

with clause for storing parameters.sql

WITH myconstants as (SELECT '5'::text as myvar FROM anywhere_unimportant)