Обсуждение: what does this do
Hi, 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. Thanks in advance, Jhnf
John <johnf@jfcomputer.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? Tim
On Thu, Jun 10, 2010 at 3:57 AM, Tim Landscheidt <tim@tim-landscheidt.de> wrote: > John <johnf@jfcomputer.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 -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug
First remove the python select * from jfcs_balancedue(parameter) f(enrolleeid varchar,course_cost decimal, paid_amt decimal) the jfcs_balancedue is a table function, f is the alias (with the column alias list/datatype of the columns returned bythe function). Doug -----Original Message----- From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of John Sent: Thursday, June 10, 2010 4:22 AM To: pgsql-sql@postgresql.org Subject: [SQL] what does this do Hi, 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. Thanks in advance, Jhnf -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
On Thursday 10 June 2010 06:46:46 am Little, Douglas wrote: > First remove the python > select * from jfcs_balancedue(parameter) f(enrolleeid varchar,course_cost > decimal, paid_amt decimal) > > the jfcs_balancedue is a table function, f is the alias (with the column > alias list/datatype of the columns returned by the function). > > Doug > > > -----Original Message----- > From: pgsql-sql-owner@postgresql.org > [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of John Sent: Thursday, > June 10, 2010 4:22 AM > To: pgsql-sql@postgresql.org > Subject: [SQL] what does this do > > Hi, > 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. > > Thanks in advance, > Jhnf Thanks folks the link you folks provided did the trick"If the function has been defined as returning the record data type,then an alias or the key word AS must be present, followed by a column" I was not aware of the syntax required for returning the record data type. Johnf
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