Hi Chris,
I have an example working without parameters, but I can't figure out how to
write one with parameters.
CREATE TYPE senators AS
(s_fname varchar,
S_lname varchar,
s_email varchar,
s_party char);
Now the actual SPROC is written:
CREATE FUNCTION getsenators()
RETURNS SETOF senators AS
'
DECLARE
r senators%rowtype;
BEGIN
FOR r IN SELECT
"s_fname","s_lname","s_email","s_party" FROM
"senate_members" LOOP
RETURN NEXT r;
END LOOP;
RETURN;
END
'
LANGUAGE 'plpgsql' VOLATILE;
Now, I have a column in my table called "s_party" which is a character type
with a length of 1. I want to modify my select statement to read SELECT
"s_fname","s_lname","s_email" FROM Senate_members WHERE "s_party" = $1
I can't seem to come up with a working example.
Here's what I'm trying to do:
CREATE FUNCTION getsenators(char)
RETURNS SETOF senators AS
'
DECLARE
r senators%rowtype;
BEGIN
FOR r IN SELECT "s_fname","s_lname","s_email" FROM
"senate_members" WHERE "s_party" = $1 LOOP
RETURN NEXT r;
END LOOP;
RETURN;
END
'
LANGUAGE 'plpgsql' VOLATILE;
When I try to type SELECT * FROM getsenators("D") I get an error message
that says the column "D" does not exist.
SQL error:
ERROR: column "D" does not exist
In statement:
select * from getsenators("D")
_______________________________________________
Mono-list maillist - [email protected]
http://lists.ximian.com/mailman/listinfo/mono-list
