PostgreSQL – Returning Table with Separate Columns from Function

functionspostgresql

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?

Best Answer

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.

`DISTINCT ON()`

Just as a side note, this is precisely what DISTINCT ON() does (not to be confused with DISTINCT)

SELECT DISTINCT ON ( expression [, ...] ) keeps only the first row of each set of rows where the given expressions evaluate to equal. The DISTINCT ON expressions are interpreted using the same rules as for ORDER BY (see above). Note that the "first row" of each set is unpredictable unless ORDER BY is used to ensure that the desired row appears first. For example

So if you were to write,

SELECT myFirstAgg(z)
FROM foo
GROUP BY x,y;

It's effectively

SELECT DISTINCT ON(x,y) z
FROM foo;
-- ORDER BY z;

In that it takes the first z. There are two important differences,

You can also select other columns at no cost of further aggregation..

SELECT DISTINCT ON(x,y) z, k, r, t, v
FROM foo;
-- ORDER BY z, k, r, t, v;

Because there is no GROUP BY you can not use (real) aggregates with it.

CREATE TABLE foo AS
SELECT * FROM ( VALUES
  (1,2,3),
  (1,2,4),
  (1,2,5)
) AS t(x,y,z);

SELECT DISTINCT ON (x,y) z, sum(z)
FROM foo;

-- fails, as you should expect.
SELECT DISTINCT ON (x,y) z, sum(z)
FROM foo;

-- would not otherwise fail.
SELECT myFirstAgg(z), sum(z)
FROM foo
GROUP BY x,y;

Don't forget `ORDER BY`

Also, while I didn't bold it then I will now

Note that the "first row" of each set is unpredictable unless ORDER BY is used to ensure that the desired row appears first. For example

Always use an ORDER BY with DISTINCT ON

Using an Ordered-Set Aggregate Function

I imagine a lot of people are looking for first_value, Ordered-Set Aggregate Functions. Just wanted to throw that out there. It would look like this, if the function existed:

SELECT a, b, first_value() WITHIN GROUP (ORDER BY z)    
FROM foo
GROUP BY a,b;

But, alas you can do this.

SELECT a, b, percentile_disc(0) WITHIN GROUP (ORDER BY z)   
FROM foo
GROUP BY a,b;

Postgresql – Get a Table from XML in postgresql

There is nothing wrong with using xpath_table().

Even though some of the functions in the xml2 extension have been replaced with in-core functions, xpath_table is not one of them (I think this will happen for Postgres 10).

Until then, xpath_table() seems to be your only option:

SELECT * 
FROM xpath_table('id',
            'documents',
            'xmldata',
            '//test',
            'true') AS t(doc_id integer, data text);

Returns:

doc_id | data    
-------+---------
     1 | Red text
     1 | green

In order for this to work you first need to create the extension (as a superuser):

create extension xml2;

Otherwise the function isn't available.

Best Answer

Related Solutions

PostgreSQL – Is There a Type-Safe first() Aggregate Function?

DISTINCT ON()

Don't forget ORDER BY

Using an Ordered-Set Aggregate Function

Postgresql – Get a Table from XML in postgresql

Related Question

`DISTINCT ON()`

Don't forget `ORDER BY`