Thread: function in place of sub-select

Hi,

I am using postgresql 7.2 on RHL7.3.

I know that we cannot return a record or multiple values from a plpgsql
procedure unless otherwise it is a triggered procedure. But i have a need
to use a function in place of sub-select, something like

select * from my_table where field1 in (select my_func());

my_func() returns a value of type text. I am literally parsing the values
in my_func() to return the value, in the format acceptable by IN operator.
Something like, "'value1', 'value2'". But it has been interpreted as it is
instead of 'value1' and 'value2' seperately and resulting in 0 record.
Again, a view instead of function in a sub-select donot help in my case,
since i have lot of processing involved in the function.

How do i accompanise it in function? Or whats the other way?

TIA.

regards,
bhuvaneswaran

Hi Bhuvan!

One way might be to create a C (or C++) program that emulates your my_func
function.

We've tried doing this and did some benchmarks that show that the system
works quite faster when you take out the stored procedures/functions and
"translate" them into C programs.

Regards,

Mel Jamero




-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org]On Behalf Of Bhuvan A
Sent: Saturday, October 12, 2002 7:52 PM
To: pgsql-admin@postgresql.org
Subject: [ADMIN] function in place of sub-select


Hi,

I am using postgresql 7.2 on RHL7.3.

I know that we cannot return a record or multiple values from a plpgsql
procedure unless otherwise it is a triggered procedure. But i have a need
to use a function in place of sub-select, something like

select * from my_table where field1 in (select my_func());

my_func() returns a value of type text. I am literally parsing the values
in my_func() to return the value, in the format acceptable by IN operator.
Something like, "'value1', 'value2'". But it has been interpreted as it is
instead of 'value1' and 'value2' seperately and resulting in 0 record.
Again, a view instead of function in a sub-select donot help in my case,
since i have lot of processing involved in the function.

How do i accompanise it in function? Or whats the other way?

TIA.

regards,
bhuvaneswaran



---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

> One way might be to create a C (or C++) program that emulates your my_func
> function.
>
> We've tried doing this and did some benchmarks that show that the system
> works quite faster when you take out the stored procedures/functions and
> "translate" them into C programs.

Rightly said. C functions work faster than the stored procedures. But C
function too wont fit my case. The interpretation, to feed data to the IN
operator cannot be performed properly. How else do we achieve this?

regards,
bhuvaneswaran