Re: another problem with stored procedures

On Thu, 29 Dec 2005, Ted Byers wrote:

> I have just encountered another problem. I am not sure if it is with my
> code, or with how I am working with Postgres/pgAdmin III.
>
> Here is another function, as created using the wizard/dialog box in pgAmin III for creating functions:
>
> CREATE FUNCTION "People".get_pw(ea "varchar") RETURNS "varchar" AS
> $BODY$
> SELECT pword FROM "People".uids WHERE email_address = ea;
> $BODY$
> LANGUAGE 'sql' VOLATILE;
>
> When I click <OK> to indicate that I am finished, I get an error message
> saying there is no column called "ea". Of course I know that; that is
> because it is a function parameter instead.

From the create function docs:
The name of an argument. Some languages (currently only PL/pgSQL) let
you use the name in the function body. For other languages the name of an
input argument is just extra documentation. But the name of an output
argument is significant, since it defines the column name in the result
row type. (If you omit the name for an output argument, the system will
choose a default column name.)

SQL language functions are definately in the "for other languages"
portion. I think you'll need to refer to it as $1 inside the function.

> What I don't understand is why
> pgAdmin would not put the "IN" qualifier for the function's only parameter
> or why Postgres would think ea is a column when the code clearly identifies
> it as a function parameter. (BTW: replacing 'sql' by 'plpgsql' has no
> effect, except the error message is even less informative).

Bare sql won't make a valid plpsql function, so you probably would get an
error at the select or some such.