2

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.

  • You're not RETURNING anything. Why would it not return an empty table? – Evan Carroll Jun 13 '17 at 22:39
1

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,

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.