10

I am trying to return multiple records using RECORD data type, is there a way I can append to RECORD and add/append a new value with each iteration to this RECORD.

that is, I want to append to rec so that rec becomes a set of rows when the loop is over, which I can just RETURN at the end of my function. Currently, I am doing this -

SELECT temp_table.col1, temp_table.col2, temp_table.col3
      INTO rec
      FROM temp_table
      WHERE temp_table.col3 = false;

my full code is here:

CREATE OR REPLACE FUNCTION validation()
  RETURNS RECORD AS $$
DECLARE
        rec RECORD;
        temp_row RECORD;
BEGIN

  CREATE TEMPORARY TABLE temp_table (col1 TEXT, col2 INTEGER, col3 BOOLEAN) ON COMMIT DROP;

  FOR temp_row IN SELECT * FROM staging.validation
  LOOP

    RAISE NOTICE 'sql: %', temp_row.sql;

    EXECUTE format('INSERT INTO temp_table %s', temp_row.sql);

    IF (SELECT DISTINCT temp_table.col3 FROM temp_table WHERE temp_table.col3 = false)=false THEN
      RAISE NOTICE 'there is a false value';

      SELECT temp_table.col1, temp_table.col2, temp_table.col3
      INTO rec
      FROM temp_table
      WHERE temp_table.col3 = false;
    END IF;


  END LOOP;
  RETURN rec;
END; $$
LANGUAGE plpgsql;

Current output after SELECT validation();

validation
(crea_ddf,8095,f)

Desired Output

validation
(crea_ddf,8095,f)
(some_source_system,some_count,f)
(some_other_source_system,some_count,f)
(.....)
  • @EvanCarroll Hi Evan, that's my question, which I posted over there too...just in case if someone misses it over here. – hky404 Sep 18 '17 at 20:23
  • I'm not sure what you're trying to do, could you explain it a bit more? – Evan Carroll Sep 18 '17 at 22:52
  • 1
    @hky404: please don't cross-post; that only causes duplication of efforts. – Martijn Pieters Sep 19 '17 at 18:01
9

The function needs to return a SETOF RECORD instead of RECORD and have one RETURN NEXT per row instead of a single RETURN, as in:

CREATE FUNCTION test() RETURNS SETOF RECORD AS $$
DECLARE
 rec record;
BEGIN
  select 1,2 into rec;
  return next rec;

  select 3,4 into rec;
  return next rec;
END $$ language plpgsql;

Caller:

=> select * from test() as x(a int ,b int) ;
 a | b 
---+---
 1 | 2
 3 | 4
(2 rows)

Note that SQL being strongly and statically typed, the RECORD pseudo-type is hard to work with.
Often it's less cumbersome to use right from the start a composite type with a full definition of names and type for each column, either with the TABLE(...) syntax for an anonymous type or with CREATE TYPE for a persistent named type.

6

Use setof record and return next rec if you want to return multiple records from a function, example:

create or replace function test_function()
    returns setof record 
    language plpgsql as $$
declare
    rec record;
begin
    for rec in
        select i, format('str%s', i), i/2*2 = i
        from generate_series(1, 3) i
    loop
        return next rec;
    end loop;
end $$;

Such a function needs to be called in the FROM clause with a column definition list:

select test_function(); -- NO

ERROR:  set-valued function called in context that cannot accept a set  

select * from test_function();  -- NO

ERROR:  a column definition list is required for functions returning "record"

select * from test_function() as (id int, str text, is_even boolean);

 id | str  | is_even 
----+------+---------
  1 | str1 | f
  2 | str2 | t
  3 | str3 | f
(3 rows)

A better option is to use returns table(...) and return query:

drop function if exists test_function();
create or replace function test_function()
    returns table (id int, str text, is_even boolean)
    language plpgsql as $$
begin
    return query
        select i, format('str%s', i), i/2*2 = i
        from generate_series(1, 3) i;
    -- you can use return query multiple times
    -- or assign values to columns
    -- and return the row:
    id = 100;
    str = 'extra';
    is_even = true;
    return next; -- without a parameter
end $$;

Usage:

select test_function(); -- possible but rather impractical

 test_function 
---------------
 (1,str1,f)
 (2,str2,t)
 (3,str3,f)
 (100,extra,t)
(4 rows)

select * from test_function();

 id  |  str  | is_even 
-----+-------+---------
   1 | str1  | f
   2 | str2  | t
   3 | str3  | f
 100 | extra | t
(4 rows)
1

This is a red flag..

  1. You have a table validation.
  2. You move the rows into a temp table staging.
  3. Any rows with a temp_table.col3 IS FALSE you return to the user
  4. Along with any other rows in a specified list of tables where that column is false.
  5. Then you drop the temp table (on commit)

Just do this..

WITH t AS ( SELECT true AS runthis FROM staging.validation WHERE col3 IS FALSE )
SELECT *
FROM staging.validation
WHERE t.runthis && col3 = 3
UNION ALL 
  SELECT *
  FROM some_source_system
  WHERE t.runthis && some_source_system.col3 = 3
UNION ALL 
  SELECT *
  FROM some_other_source_system
  WHERE t.runthis && some_other_source_system.col3 = 3;

You can even put that in a VIEW if you want

As a side note

SELECT DISTINCT temp_table.col3
FROM temp_table
WHERE temp_table.col3 = false

What does DISTINCT here do? Just do LIMIT one. In fact, I would argue that this is even cleaner.

SELECT true
FROM temp_table
WHERE temp_table.col3 = false
LIMIT 1;

Then you don't need the weird = false ) = FALSE

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.