SEO

#1 (**permalink**) 04-09-2008, 07:51 AM

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

#2 (**permalink**) 04-09-2008, 07:52 AM

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/i...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/i...AX-IDENTIFIERS
http://www.postgresql.org/docs/faqs.FAQ.html#item4.21

--
Michael Fuhr

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

How to use parameters in stored procedures?