Thread: Functions replicating stored procedures

I am attemping to create functions to replace stored procedures from MSSQL
server...  the only way that I could achieve this was to create a function
such as

CREATE FUNCTION test_function(int) returns SETOF countries AS
  'SELECT country_id, country_name FROM countries WHERE country_id > $1'
LANGUAGE SQL;

the only problem with this is that in order to obtain the results from this
I need to call the function as

SELECT country_id(test_function(0)), country_name(test_function(0));

by doing this am I forcing test_function to be called every time I want to
get a field name out of it?  If so this must be less efficient that merely
passing a SQL string to the database server and allowing it to parse it.

in addition, I have also tried to copy the value by doing

SELECT test_function(0) AS val, country_id(val), country_name(val);

but that would not run...

Basically my question is am I calling the stored procedure once for every
field, and if so, is there another way to return a multiple row multiple
field rowsets or should I just pass all my queries in text form to the
database server to increase efficiency...

Thanks, Joshua Moore-Oliva.

I've not personally used functions that return setof, but I'd imagine it
needs to be used in a from clause, e.g.:
SELECT * FROM test_function(5);
Also I'm assuming that countries just has country_id and country_name as
attributes as you are returning rows with the same form as countries. If not
then create a table with just country_id and country_name and return a set
of this.
hope this helps,
- Stuart

> -----Original Message-----
> From: Joshua Moore-Oliva [mailto:chatgris@mediapow.com]
> Sent: 08 May 2002 00:31
> To: pgsql-novice@postgresql.org
> Subject: [NOVICE] Functions replicating stored procedures
>
>
> I am attemping to create functions to replace stored
> procedures from MSSQL
> server...  the only way that I could achieve this was to
> create a function
> such as
>
> CREATE FUNCTION test_function(int) returns SETOF countries AS
>   'SELECT country_id, country_name FROM countries WHERE
> country_id > $1'
> LANGUAGE SQL;
>
> the only problem with this is that in order to obtain the
> results from this
> I need to call the function as
>
> SELECT country_id(test_function(0)), country_name(test_function(0));
>
> by doing this am I forcing test_function to be called every
> time I want to
> get a field name out of it?  If so this must be less
> efficient that merely
> passing a SQL string to the database server and allowing it
> to parse it.
>
> in addition, I have also tried to copy the value by doing
>
> SELECT test_function(0) AS val, country_id(val), country_name(val);
>
> but that would not run...
>
> Basically my question is am I calling the stored procedure
> once for every
> field, and if so, is there another way to return a multiple
> row multiple
> field rowsets or should I just pass all my queries in text form to the
> database server to increase efficiency...
>
> Thanks, Joshua Moore-Oliva.
>