How can I use query result stored into a record type variable for another query within the same stored function? I use Postgres 9.4.4.
With a table like this:
create table test (id int, tags text[]);
insert into test values (1,'{a,b,c}'),
(2,'{c,d,e}');
I wrote a function (simplified) like below:
CREATE OR REPLACE FUNCTION func(_tbl regclass)
RETURNS TABLE (t TEXT[], e TEXT[])
LANGUAGE plpgsql AS $$
DECLARE
t RECORD;
c INT;
BEGIN
EXECUTE format('SELECT id, tags FROM %s', _tbl) INTO t;
SELECT count(*) FROM t INTO c;
RAISE NOTICE '% results', c;
SELECT * FROM t;
END
$$;
... but didn't work:
select func('test');
ERROR: 42P01: relation "t" does not exist LINE 1: SELECT count(*) FROM t ^ QUERY: SELECT count(*) FROM t CONTEXT: PL/pgSQL function func(regclass) line 7 at SQL statement LOCATION: parserOpenTable, parse_relation.c:986
The core misunderstanding: a record
variable holds a single row (or is NULL), not a table (0-n rows of a well-known type). There are no "table variables" in Postgres or PL/pgSQL. Depending on the task, there are various alternatives:
Accordingly, you cannot assign multiple rows to a record
type variable. In this statement:
EXECUTE format('SELECT id, tags FROM %s', _tbl) INTO t;
... Postgres assigns only the first row and discards the rest. Since "the first" is not well defined in your query, you end up with an arbitrary pick. Obviously due to the misunderstanding mentioned at the outset.
A record
variable also cannot be used in place of tables in SQL queries. That's the primary cause of the error you get:
relation "t" does not exist
It should be clear by now, that count(*)
wouldn't make any sense to begin with, since t
is just a single record / row - besides being impossible anyway.
Finally (even if the rest would work), your return type seems wrong: . Since you select (t TEXT[], e TEXT[])
id, tags
into t
, you'd want to return something like (id int, e TEXT[])
.
What you are trying to do would work like this:
CREATE OR REPLACE FUNCTION func(_tbl regclass)
RETURNS TABLE (id int, e text[]) AS
$func$
DECLARE
_ct int;
BEGIN
EXECUTE format(
'CREATE TEMP TABLE tmp ON COMMIT DROP AS
SELECT id, tags FROM %s'
, _tbl);
GET DIAGNOSTICS _ct = ROW_COUNT; -- cheaper than another count(*)
-- ANALYZE tmp; -- if you are going to run multiple queries
RAISE NOTICE '% results', _ct;
RETURN QUERY TABLE tmp;
END
$func$ LANGUAGE plpgsql;
Call (note the syntax!):
SELECT * FROM func('test');
Related:
Just a proof of concept. While you are selecting the whole table, you would just use the underlying table instead. In reality you'll have some WHERE
clause in the query ...
Careful of the lurking type mismatch, count()
returns bigint
, you couldn't assign that to an integer
variable. Would need a cast: count(*)::int
.
But I replaced that completely, it's cheaper to run this right after EXECUTE
:
GET DIAGNOSTICS _ct = ROW_COUNT;
Why ANALYZE
?
Aside: CTEs in plain SQL can often do the job: