Thread: what does this do

what does this do

From

John

Date:

10 June 2010, 09:29:57

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

Re: what does this do

From

Tim Landscheidt

Date:

10 June 2010, 10:57:34

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

Re: what does this do

From

Richard Broersma

Date:

10 June 2010, 13:42:38

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

Re: what does this do

From

"Little, Douglas"

Date:

10 June 2010, 13:46:59

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

Re: what does this do

From

John

Date:

10 June 2010, 14:04:08

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

Re: what does this do

From

Tim Landscheidt

Date:

10 June 2010, 14:57:15

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