Thread: can Function return a row ???

Dear postgresql Developers ..I need your help regarding postgresql funtion return type.I need to build a stored procedure like this

"Select First_Name,Last_Name,Designation,Department,Telephone_No,Fax_No From "Document""

But I can't get this whole row ...i can only extract single column like this

SELECT "First_Name"(STP_SelectAllFromdocument());

I am using postgresql 7.2 alongwith pgaccess.plz let me know how can i return a rowset from a function.

Tahira Aslam wrote:
> Dear postgresql Developers ..I need your help regarding postgresql
> funtion return type.I need to build a stored procedure like this
>
> "Select First_Name,Last_Name,Designation,Department,Telephone_No,Fax_No
>  From "Document""
>
> But I can't get this whole row ...i can only extract single column like this
>
> SELECT "First_Name"(STP_SelectAllFromdocument());
>
> I am using postgresql 7.2 alongwith pgaccess.plz let me know how can i
> return a rowset from a function.
>

Sorry, but returning a composite (row) type does not work in 7.2.x or
before. It is being worked on now, and should be available in 7.3 when
it's released.

Joe

Thanks Joe for your timly help..but still i have a little confusion.I read postgresql 7.2 Documentation for PL/pgsql (chapter 23)...its about "Cursors" but it says something like this also

"A more interesting possibility is that a function can return a reference to a cursor it has set up,allowing the caller to read the rows. This provides one way of returning a rowset from a function"

plz I need further guidance in this regard..

Thanks

Tahira Aslam

  Joe Conway <mail@joeconway.com> wrote:

Tahira Aslam wrote:
> Dear postgresql Developers ..I need your help regarding postgresql
> funtion return type.I need to build a stored procedure like this
>
> "Select First_Name,Last_Name,Designation,Department,Telephone_No,Fax_No
> From "Document""
>
> But I can't get this whole row ...i can only extract single column like this
>
> SELECT "First_Name"(STP_SelectAllFromdocument());
>
> I am using postgresql 7.2 alongwith pgaccess.plz let me know how can i
> return a rowset from a function.
>

Sorry, but returning a composite (row) type does not work in 7.2.x or
before. It is being worked on now, and should be available in 7.3 when
it's released.

Joe




---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Tahira Aslam wrote:
>   Thanks Joe for your timly help..but still i have a little confusion.I
> read postgresql 7.2 Documentation for PL/pgsql (chapter 23)...its about
> "Cursors" but it says something like this also
>
> *"A more interesting possibility is that a function can return a
> reference to a cursor it has set up,allowing the caller to read the
> rows. This provides one way of returning a rowset from a function" *
>
> plz I need further guidance in this regard..
>
> Thanks
>
> Tahira Aslam

I don't think there is an example in the 7.2 docs, but see:
http://developer.postgresql.org/docs/postgres/plpgsql-cursors.html

I think this also works in 7.2.

HTH,

Joe