Thread: How to use parameters in stored procedures?
Hi guys, I'm very new to PostgresSQL, coming from MS SQL. I'm trying to write a function that will select a row from a location: CREATE FUNCTION SelectLocation(IN "myLocationID" int4) RETURNS "Location" LANGUAGE 'sql' AS $BODY$ SELECT "LocationID", "Name", "PhoneNumber", "FaxNumber", "EmailAddress", "StreetAddress", "City", "Province", "PostalCode" FROM "Location" WHERE "LocationID" = "myLocationID" ORDER BY "Name"; $BODY$ I get an error that the column myLocationID doesn't exist. I'm sure it's something simple, but I can't figure it out. Also too, why do I have to quote columns and tables? Thanks guys, Mike
On Fri, Jan 13, 2006 at 09:53:29AM -0800, Cyphos wrote: > I get an error that the column myLocationID doesn't exist. I'm sure > it's something simple, but I can't figure it out. Not all procedural languages support named arguments. Apparently SQL functions don't, so use $1, $2, etc. http://www.postgresql.org/docs/8.1/interactive/xfunc-sql.html > Also too, why do I have to quote columns and tables? You don't have to...unless you created them that way. See the discussion of quoted identifiers in the documentation and in the FAQ: http://www.postgresql.org/docs/8.1/interactive/sql-syntax.html#SQL-SYNTAX-IDENTIFIERS http://www.postgresql.org/docs/faqs.FAQ.html#item4.21 -- Michael Fuhr