1

I'm using the squirrel sql client to work with Postgres and the problem is that the same query returns different results.

If a query below execute as is, it works fine:

select foo.column1 as Field1, 1 as Field2 from (values (3343),(45323)) as Foo

But if the query execute from stored function like this one:

CREATE OR REPLACE FUNCTION getSomeData(text) RETURNS setof tmp_stub_type AS
$body$
DECLARE
 r tmp_stub_type%rowtype;
BEGIN
    FOR r IN
      select foo.column1 as Field1, 1 as Field2 from (values (3343),(45323)) as Foo
    LOOP
      RETURN NEXT r;
    END LOOP;
    RETURN;
END;
$body$
LANGUAGE 'plpgsql';

It returns the same rows count but with UnknownType values with one field instead two.

tmp_stub_type is normal table with two integer fields and nothing more.

I tried to solve it by the Postgres pgAdmin, but he showed me the same thing except the values - they was right but placed in one field and separated by comma.

I need run the query inside stored function, please help deal with it and sorry for my english.

i'm using: Postgres 9.3, Squirrel 3.5.3, OS Windows

drewango
  • 25
  • 6

1 Answers1

3

To get multiple columns from a function that returns a row type or has multiple output parameters, use e.g. SELECT * FROM getSomeData(...) instead of SELECT getSomeData(...).

Source: http://www.postgresql.org/docs/9.3/static/plpgsql-control-structures.html#AEN58217

tsnorri
  • 1,966
  • 5
  • 21
  • 29