anonymous composite types for Table Functions (aka SRFs)

From: Joe Conway <mail(at)joeconway(dot)com>
To: pgsql-patches(at)postgresql(dot)org
Subject: anonymous composite types for Table Functions (aka SRFs)
Date: 2002-07-29 05:24:34
Message-ID: 3D44D192.2030407@joeconway.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-patches

Attached are two patches to implement and document anonymous composite
types for Table Functions, as previously proposed on HACKERS. Here is a
brief explanation:

1. Creates a new pg_type typtype: 'p' for pseudo type (currently either
'b' for base or 'c' for catalog, i.e. a class).

2. Creates new builtin type of typtype='p' named RECORD. This is the
first of potentially several pseudo types.

3. Modify FROM clause grammer to accept:
SELECT * FROM my_func() AS m(colname1 type1, colname2 type1, ...)
where m is the table alias, colname1, etc are the column names, and
type1, etc are the column types.

4. When typtype == 'p' and the function return type is RECORD, a list
of column defs is required, and when typtype != 'p', it is disallowed.

5. A check was added to ensure that the tupdesc provide via the parser
and the actual return tupdesc match in number and type of attributes.

When creating a function you can do:
CREATE FUNCTION foo(text) RETURNS setof RECORD ...

When using it you can do:
SELECT * from foo(sqlstmt) AS (f1 int, f2 text, f3 timestamp)
or
SELECT * from foo(sqlstmt) AS f(f1 int, f2 text, f3 timestamp)
or
SELECT * from foo(sqlstmt) f(f1 int, f2 text, f3 timestamp)

Included in the patches are adjustments to the regression test sql and
expected files, and documentation.

If there are no objections, please apply.

Thanks,

Joe

p.s.
This potentially solves (or at least improves) the issue of builtin
Table Functions. They can be bootstrapped as returning RECORD, and
we can wrap system views around them with properly specified column
defs. For example:

CREATE VIEW pg_settings AS
SELECT s.name, s.setting
FROM show_all_settings()AS s(name text, setting text);

Then we can also add the UPDATE RULE that I previously posted to
pg_settings, and have pg_settings act like a virtual table, allowing
settings to be queried and set.

Attachment Content-Type Size
pseudo_type.2002.07.28.1.patch text/plain 50.0 KB
pseudo_type_doc.2002.07.28.1.patch text/plain 5.0 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2002-07-29 06:18:48 Re: [GENERAL] Stats Collector
Previous Message Christopher Kings-Lynne 2002-07-29 03:51:25 Re: [GENERAL] Stats Collector

Browse pgsql-patches by date

  From Date Subject
Next Message Christopher Kings-Lynne 2002-07-29 07:04:41 New contrib: pg_reset_stats()
Previous Message Peter Eisentraut 2002-07-28 15:21:31 Re: [PATCHES] prepareable statements