Hello,
My company is thinking about converting from Sybase to PostgresQL. We have
PostgresQL 7.0.2 installed and are trying to convert our tables, triggers and
stored procedures to it.
We are having the most trouble with triggers and stored procedures (functions).
If anyone could answer our questions, we'd really appreciate it.
1. How do i retreive multiple values from a function.
In Sybase the following procedure will return 3 values:
-------------------------------------------------------
create procedure get_Person(@id numeric(12))
as
begin
select name, nickName, address
from Person
where id = @id
end
-------------------------------------------------------
All I have to do is add another field name to the select statement
and the procedure will then return it too.
The closest things I can find in PostgresQL are:
a. SETOF, which is mentioned in the docs, but no examples of it's usage are
given. An example would be helpful.
b. returning a row, such as:
--------------------------------
create function get_person(int4)
returns person
as
'select * from person where id = $1;'
language 'sql';
--------------------------------
which really isn't that helpful because I still have to make multiple
calls to get_person() to get multiple values. Correct?
mydb=> select name(get_person(1));
mydb=> select nickName(get_person(1));
mydb=> select address(get_person(1));
2. Can a trigger return values?
Again, in Sybase I can do this:
-------------------------------
create trigger
Person_insert
on Person
for insert
as
begin
select id
from inserted
end
-------------------------------
Everytime I insert a record into Person, it's id is returned to the caller.
As far as I can tell from the docs, all a PostgresQL trigger can do is validate
and change the row being inserted/updated/deleted.
Is that correct?
3. What does the OPAQUE return type mean.
I understand the functions used as triggers must return the OPAQUE data
type. What exactly is the OPAQUE data type?
Thanks for any help.
--craig
+-------------------------------------------------------+
| Craig N. Caroon | Nando Media |
| Senior Developer | http://www.nandomedia.com |
| caroon@nandomedia.com | The Nando Times |
| (919) 836-2885 | http://www.nandotimes.com |
+-------------------------------------------------------+
