Postgres – Fix Empty Table Returned with plpgsql and EXECUTE USING

plpgsqlpostgresql

Why this function return an empty table (while running the plain query from command line returns different values):

CREATE OR REPLACE FUNCTION get_area_values (_tbl regclass, _column text, indicator_id integer) RETURNS
TABLE (indicator integer, postcode text, date date, value double precision) AS $$
        BEGIN
                execute format(
                'WITH ad AS (select distinct area, district from postcode_lookup)'
                || ' select $1 as indicator, ad.area as postcode, t.date, sum(t.%I) as value'
                || ' from %I as t'
                || ' join ad on t.postcode = ad.district group by ad.area, t.date', _column, _tbl)
                USING indicator_id;
        END
$$ LANGUAGE plpgsql;

I had even tried to use execute with || without any luck.

Best Answer

I think you want RETURN QUERY EXECUTE ... USING context doesn't matter: EXECUTE .. USING won't work from psql, it will not append the result to the functions return value.

This returns an empty table, and should probably trigger a warning when you create such a function.

CREATE FUNCTION foo ()
RETURNS TABLE (a int) AS $$
BEGIN
  EXECUTE 'SELECT $1' USING 42;
END
$$ LANGUAGE plpgsql;

This returns a table with one row, with one int column a with a value of 42,

CREATE FUNCTION foo ()
RETURNS TABLE (a int) AS $$
BEGIN
  RETURN QUERY EXECUTE 'SELECT $1;' USING 42;
END
$$ LANGUAGE plpgsql;

See also,

Related Question