1

I have this function, it's simple enough:

CREATE OR REPLACE FUNCTION test_function()
RETURNS TABLE(
        id INTEGER,
        organization_id INTEGER,
        email_address CHARACTER VARYING
)
AS $BODY$
        SELECT id, organization_id, email_address FROM users;

$BODY$ LANGUAGE SQL;

The output of this functions gives me something that looks like this:

> SELECT test_function();                                                                                                                                                                                                          test_function 
---------------
 (1,1,test)
 (3,1,test2)
 (4,2,2test)

When I run the query by itself, I expect to get something more like this:

> select id, organization_id, email_address FROM users;
 id | organization_id | email_address 
----+-----------------+---------------
  1 |               1 | test
  3 |               1 | test2
  4 |               2 | 2test

How can I get a similar result back like that so I can parse it in a SQL function?

1

To get decomposed, separate columns, call the table function with:

SELECT * FROM test_function();

But you also have sneaky naming conflicts. The column names listed in the RETURNS TABLE clause are implicit OUT parameters, visible in every SQL statement in the function body. Avoid using names that conflict with column names and/or table-qualify all possibly conflicting table column names in the function body. Like:

CREATE OR REPLACE FUNCTION test_function()
  RETURNS TABLE (id int, organization_id int, email_address varchar) AS
$func$
SELECT u.id, u.organization_id, u.email_address FROM users u;
$func$ LANGUAGE SQL;

See: Variable Substitution in the manual.

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.