How to use record type variable in plpgsql?

Question

How can I use a query result stored in a record type variable for another query in the same stored function? I am using 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) as shown 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 does not 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 main misunderstanding: the record variable contains one row (or NULL), and not a table (0-line rows of a known type). In Postgres or PL / pgSQL there are no "table variables". Depending on the task, there are various options:

• PostgreSQL Variable Table
• SELECT multiple rows and columns into a record variable

Accordingly, you cannot assign multiple strings to a variable of type record . In this statement:

 EXECUTE format('SELECT id, tags FROM %s', _tbl) INTO t; 

... Postgres only assigns the first line and discards the rest. Since the "first" is not defined in your request, you get an arbitrary choice. Obviously due to the misunderstanding mentioned at the very beginning.

A record variable also cannot be used instead of tables in SQL queries. This is the main reason for the error you receive:

relation "t" does not exist

It should now be clear that count(*) does not make sense to start with the fact that t is just one record / line - except that this is not possible at all.

Finally (even if the rest works), your return type seems wrong: (t TEXT[], e TEXT[]) . Since you select id, tags at t , you want to return something like (id int, e TEXT[]) .

What you are trying to do will work as follows :

 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; 

The call (note the syntax!) :

 SELECT * FROM func('test'); 

on this topic:

• Postgres creates a local temp table (when commit commit) from sql dynamic row

Just a proof of concept. As long as you select the whole table, instead you just use the base table. In fact, you will have a WHERE in the request ...

Beware of the hidden mismatch type, count() returns bigint , you cannot assign it to an integer variable. You will need a throw: count(*)::int .

But I completely replaced it, it’s cheaper to run this right after EXECUTE :

 GET DIAGNOSTICS _ct = ROW_COUNT; 

Details in the manual.

Why ANALYZE ?

• Are regular VACUUM ANALYSISes still recommended in section 9.1?

---

In addition: CTEs in simple SQL can often do the job:

• Migrating from FOR loops in plpgsql to a set-based SQL command