PostgreSQL return an Array or Record as a Row

Question

I'm trying to return a variable with a PostgreSQL function that returns row/rows so I can use libpqxx on the client side to iterate over it for example using:

for (pqxx::result::const_iterator row = result.begin(); row != result.end(); row++)
    {
        for (pqxx::const_row_iterator field = row.begin(); field != row.end(); field++) 
            {
                cout << field << '\n';
            }
        }

This is my PostgresSQL function:

CREATE OR REPLACE FUNCTION seal_diff_benchmark_pgsql(sealparams CHARACTER VARYING) RETURNS RECORD AS $outputVar$
DECLARE
    tempVar1 CHARACTER VARYING;
    tempVar2 CHARACTER VARYING;
    outputVar1 TEXT[];
    outputVar record;
    sealArray TEXT[];
    execTime NUMERIC[];
BEGIN
    FOR i IN 1..2 LOOP
        SELECT "Pickup_longitude", "Dropoff_longitude" INTO tempVar1, tempVar2 FROM public.nyc2015_09_enc WHERE id=i;
        sealArray := (SELECT public.seal_diff_benchmark(tempVar1, tempVar2, sealparams));
        outputVar1[i] := sealArray[1];
        execTime[i] := sealArray[2];
    END LOOP;

    SELECT UNNEST(outputVar1) INTO outputVAR;
    RETURN outputVar;
END;
$outputVar$ LANGUAGE plpgsql;

I also tried returning outputVar1 as TEXT[]. My field variable on the client side holds {foo, bar} if I use returns TEXT[] or (foo) if I use returns RECORD. But this is not what I need, which is a row like return from a TEXT[] array or a RECORD variable without any (), [], {} chars at the beginning and at the end of the output.

How can I change my PostgreSQL function to make it work? I think I'm missing something but I can't see what.

Are you trying to make this function return set of rows with one text column (as if it was single column table)? Could you clarify about text array? Is that function supposed to return text array or set of records with arrays of text or set of records with text column or maybe a mix of those? — Łukasz Kamiński
– Łukasz Kamiński, Commented Aug 28, 2018 at 8:07
@ŁukaszKamiński, the function constructs an array of texts outputVar1 and I needed it to be returned as if it was a single column table, so I can iterate over it on the client side with the libpqxx library. — TalG
– TalG, Commented Aug 28, 2018 at 8:42

Łukasz Kamiński · Accepted Answer · 2018-08-28 09:55:19Z

1

There are many approaches to do what you want.

If it really is just one column that you want, then you can simply do:

CREATE OR REPLACE FUNCTION seal_diff_benchmark_pgsql(sealparams CHARACTER VARYING) 
RETURNS SETOF TEXT AS $outputVar$
DECLARE
    tempVar1 CHARACTER VARYING;
    tempVar2 CHARACTER VARYING;
    sealArray TEXT[];
    execTime NUMERIC[];
    outputVar text;
BEGIN
    FOR i IN 1..2 LOOP
        SELECT "Pickup_longitude", "Dropoff_longitude" INTO tempVar1, tempVar2
          FROM public.nyc2015_09_enc WHERE id=i;
        sealArray := (SELECT public.seal_diff_benchmark(tempVar1, tempVar2, sealparams));
        execTime[i] := sealArray[2];
        FOREACH outputVar IN ARRAY sealArray[1] LOOP --iterate over that text array
          RETURN NEXT outputVar;
        END LOOP;
    END LOOP;
END;
$outputVar$ LANGUAGE plpgsql;

Returned colum will be named just like the function.

SELECT seal_diff_benchmark_pgsql FROM seal_diff_benchmark_pgsql('stuff');
-- alternative
SELECT seal_diff_benchmark_pgsql('stuff');

You can also specify columns in function parameters:

CREATE OR REPLACE FUNCTION seal_diff_benchmark_pgsql(sealparams CHARACTER VARYING, OUT outputVar text)

Then returned column will be named outputVar. In case of returning just one column, Postgres forces RETURNS to be of that column type, so in this case SETOF TEXT or just TEXT if one row is expected. If you return more than one column, then you need to use RETURNS SETOF RECORD.

When you use named columns in function parameters, then you need to assign values to them just like you would to variables from DECLARE section:

LOOP
  outputVar := 'some value';
  outputVar2 := 'some value';
  outputVar3 := 'some value';
  RETURN NEXT;
END LOOP;

There are a few other examples on how to return sets from functions in my old answer here: How to return rows of query result in PostgreSQL's function?

answered Aug 28, 2018 at 9:55

Łukasz Kamiński

6,0401 gold badge26 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

TalG Over a year ago

Thanks it worked! In my case I needed to take this part FOREACH outputVar IN ARRAY outputArray LOOP --iterate over that text array RETURN NEXT outputVar; END LOOP; outside the first loop and loop over an array because sealArray[1] just text, which that I actually could reduce more code.

TalG Over a year ago

I'm having a different issue though. I've added this in the first loop for testing

INSERT INTO public.runtime_benchmark(test_number, column_names, execution_time, operation_type, seal_or_sql)  			VALUES (1, 'Pickup_longitude, Dropoff_longitude', 1, 'sub', 'seal');

and I'll be using a slight different version of it later, but nothing gets inserted into the table. Surprisingly everything works fine if I execute the command in a new query window. Do you know what could be causing it? My user has all the right permissions for the public.runtime_benchmark table.

Łukasz Kamiński Over a year ago

@TalG If function works, then I think there could be 2 reasons for INSERT to silently do nothing: trigger on INSERT or RLS on table. As for the iterating over array, I made a mistake and its not really needed here, you can do in your first loop at the end RETURN NEXT sealArray[1]; and remove that foreach at the end of function.

TalG Over a year ago

The user executing the function is also the owner of the table public.runtime_benchmark table. What do you mean by Trigger on INSERT?

Łukasz Kamiński Over a year ago

It is procedure executed when certain conditions are met. You would have to create such trigger, so if you made table and don't know about triggers then there is none. You can read about them here: postgresql.org/docs/current/static/plpgsql-trigger.html

|

Collectives™ on Stack Overflow

PostgreSQL return an Array or Record as a Row

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related