CODEWITHSUNDEEP
c++postgresqllibpqlibpqxx
TalG
TalG

Reputation: 748

PostgreSQL return an Array or Record as a Row

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.

Upvotes: 2

Views: 7028

Answers (1)

Łukasz Kamiński
Łukasz Kamiński

Reputation: 5930

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?

Upvotes: 1

Related Questions