Functions replicating stored procedures

From: "Joshua Moore-Oliva" <chatgris(at)mediapow(dot)com>
To: <pgsql-novice(at)postgresql(dot)org>
Subject: Functions replicating stored procedures
Date: 2002-05-07 23:30:40
Message-ID: 002701c1f61f$32e5bae0$4e082b18@cr14010a
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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.

Browse pgsql-novice by date

  From Date Subject
Next Message D. Duccini 2002-05-08 00:31:03 more voodoo planner bs :)
Previous Message Josh Berkus 2002-05-07 18:15:47 Re: Stored Procedures