postgresqlfunctionvariablesplpgsqltable-variable

How to use a record type variable in plpgsql?

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
Solution

  • 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: (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:

    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?

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