In PostgreSQL how do I create a function that returns an actual table?

Question

For example, if I have the following function:

CREATE OR REPLACE FUNCTION cols(_table_name TEXT)
RETURNS TABLE (column_name TEXT,
               data_type TEXT) AS
$$

SELECT column_name,
       data_type
FROM information_schema.columns
WHERE table_name = _table_name
ORDER BY column_name
;

$$ LANGUAGE sql;

And the following table:

CREATE TABLE test (a TEXT, b INTEGER, c NUMERIC);

If I run SELECT cols('test'); I get:

    cols
-------------
 (a,text)
 (b,integer)
 (c,numeric)
(3 rows)

But I would like to be able to get:

 column_name | data_type
-------------+-----------
 a           | text
 b           | integer
 c           | numeric
(3 rows)

@jdigital That did solve my problem but I still don't know if it fully answered my question. Also, how did you find that? Because I searched on Google, and SO, and was thorough looking through the similar questions links. — Gregory Arenius, Jun 23 '16 at 02:35
I did a google search for "postgres return table". I wasn't familiar with this and wanted to learn more. — jdigital, Jun 23 '16 at 02:57
Perhaps this will help with the rest of your question: you're doing a SELECT without a FROM, see here: [What does a SELECT statement without FROM used for?](http://stackoverflow.com/questions/24155475/what-does-a-select-statement-without-from-used-for) — jdigital, Jun 23 '16 at 03:06

score 4 · Accepted Answer · answered Jun 23 '16 at 05:21

4

The function is fine, it's how you're calling it.

Instead of:

SELECT cols('test');

use

SELECT * FROM cols('test');

answered Jun 23 '16 at 05:21

Craig Ringer

307,061
76
688
778

In PostgreSQL how do I create a function that returns an actual table?

1 Answers1