Re: what does this do

Richard Broersma <richard.broersma@gmail.com> wrote:

>>> I have a piece of python code that excutes a SQL statement:

>>> apitempCur.execute("select * from jfcs_balancedue('%s') f(enrolleeid varchar,
>>> course_cost decimal, paid_amt decimal)" % (enrollIds,));

>>> The "enrollids" is a list of primary keys and the "jfcs_balancedue" is a user
>>> defined function.  What I don't understand is the "f(enrolleeid
>>> varchar, ...)"   I have no idea what it's for?  Would some kind soul educate
>>> me.

>> You can omit the "AS" from "table_name AS alias
>> (column_alias, ...)", but AFAIK PostgreSQL doesn't support
>> specifying a data type for each column. Which DBMS is this
>> code used for?

> Well, it doesn't support data-types in the alias declaration for all
> set returning relations with the exception of a set returning function
> (i.e. store procedure).  The from clause has a give-away that this is
> a set returning function: "jfcs_balancedue('%s')" since it has a
> parameter.

> Notice the function name section taken from the from clause:

> http://www.postgresql.org/docs/9.0/static/sql-select.html#SQL-FROM

Another lesson learned :-). But it applies strictly to *re-
cord* returning functions, doesn't it? Because I had tested
generate_series() prior to my reply:

| tim=# SELECT ID FROM generate_series(1, 2) AS G(ID);
|  id
| ----
|   1
|   2
| (2 Zeilen)

| tim=# SELECT ID FROM generate_series(1, 2) AS G(ID INT);
| ERROR:  a column definition list is only allowed for functions returning "record"
| ZEILE 1: SELECT ID FROM generate_series(1, 2) AS G(ID INT);
|                         ^
| tim=#

but didn't follow the (now obvious) clue ...

Tim