How to use a record type variable in plpgsql?

Question

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 

Answer 1

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:

• PostgreSQL table variable
• SELECT multiple rows and columns into a record variable

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: (t TEXT[], e TEXT[]). Since you select 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:

• Postgres creating a local temp table (on commit drop) from a dynamic sql string

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;  

Details in the manual.

Why ANALYZE?

• Are regular VACUUM ANALYZE still recommended under 9.1?

---

Aside: CTEs in plain SQL can often do the job:

• Switching from FOR loops in plpgsql to set-based SQL commands