[Mono-list] Npgsql.dll

[Carl Olsen]

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")