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

Centenarian

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[])
  LANGUAGE plpgsql 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 tmp is big and/or you are going to run many queries

   RAISE NOTICE '% results', _ct;

   RETURN QUERY
   TABLE tmp;  -- shorthand for "SELECT * FROM tmp"
END
$func$;

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 the count completely. It's cheaper to run GET DIAGNOSTICS right after EXECUTE. Details in the manual.

Why ANALYZE?

• Are regular VACUUM ANALYZE still recommended under 9.1?

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

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

Soke

Thank you so much, Erwin, for kind and detailed answer. Now the reason is very clear to me. I will use CTE for the purpose. – Centenarian

Answer 2

Here are few ways i have used RECORD type variable in plpgsql in redshift database

1. running a loop
2. storing a record variable

Code type 1

    $body$
    declare 
    
    arow record ;
    
    begin 
    for arow in ({table})    -- use of record type
      loop
      update #some_table 
        
       column1_some_table := arow.column1,
       column2_some_table := arow.column2 
       where some_condition
      
      end loop ; 
   end ;
   $body$;
  

Code type 2

    $body$
    declare 
    
    arow record ;
    counter int := 0;
    max_counter int := 0;
    begin 
    while (counter <= max_counter)
    loop
    select column1, column2 into arow   -- use of record type
      from {table}
      update #some_table 
        
       column1_some_table := arow.column1,
       column2_some_table := arow.column2 
       where some_condition
      counter = counter+1
      end loop ; 
   end ;
   $body$;

Crisper