SEO

#1 (**permalink**) 04-09-2008, 02:33 PM

Hi,

I have a stored procedure which returns a SETOF RECORD.
so basically a partial rowtype from a table.

to execute the query in PHP, i must write :
select * from myschema.sp_a_002('username') as result(Column1 varchar);

to get the result.

However, is there another to get the result without using 'as result(column1
varchar)' ?
something like a simple "select * from storedprocedure(param);", for example
:-)

thanks a lot,

--
Alain
------------------------------------
Windows XP SP2
PostgreSQL 8.1.4
Apache 2.0.58
PHP 5

#2 (**permalink**) 04-09-2008, 02:33 PM

On 3/13/07, Alain Roger <raf.news@gmail.com> wrote:
>
> Hi,
>
> I have a stored procedure which returns a SETOF RECORD.
> so basically a partial rowtype from a table.
>
> to execute the query in PHP, i must write :
> select * from myschema.sp_a_002('username') as result(Column1 varchar);
>
> to get the result.
>
> However, is there another to get the result without using 'as
> result(column1 varchar)' ?
> something like a simple "select * from storedprocedure(param);", for
> example :-)
>
> thanks a lot,
>
> --
> Alain
> ------------------------------------
> Windows XP SP2
> PostgreSQL 8.1.4
> Apache 2.0.58
> PHP 5

That's what I do in PHP

if it returns a set "SELECT * FROM myfunction(arg1,arg2,...)"

Then I can just iterate over every record/line/row.

I don't have any stored proc that actually returns a setof record in any of
my functions though, but I would imagine that it doesn't make a difference
in how you call it.

Could you past the whole function?

David

#3 (**permalink**) 04-09-2008, 02:34 PM

# raf.news@gmail.com / 2007-03-13 18:29:36 +0100:
> Hi,
>
> I have a stored procedure which returns a SETOF RECORD.
> so basically a partial rowtype from a table.
>
> to execute the query in PHP, i must write :
> select * from myschema.sp_a_002('username') as result(Column1 varchar);
>
> to get the result.
>
> However, is there another to get the result without using 'as result(column1
> varchar)' ?
> something like a simple "select * from storedprocedure(param);", for example
> :-)

Yes, create a type, and have the function return SETOF your-type. You
need to name the types *somewhere*.

--
How many Vietnam vets does it take to screw in a light bulb?
You don't know, man. You don't KNOW.
Cause you weren't THERE. http://bash.org/?255991

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Stored procedure