Обсуждение: PL/PGSQL - character varying as function argument

I'm fairly new at PL/PGSQL and I'm trying to create a login function.
I want to pass the username and password to the function and return
the permission type that user has. Here's a shortened version of the
function with just the part giving me problems.


CREATE OR REPLACE FUNCTION user_checkCredentials(character varying)
RETURNS character varying AS
$BODY$
DECLARE
     username ALIAS FOR $1;
     permission record;
BEGIN
    select into permission permtype from users;
    RETURN permission.permtype;
END;
 $BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;

When I try to run it with:
select user_checkCredentials("asdf");

I get the error:
ERROR:  column "asdf" does not exist
LINE 1: select user_checkCredentials("asdf");


However if I use numbers, say an integer, and change to
user_checkCredentials(integer) and pass a number it works. I obviously
don't understand how function arguments work in PL/PGSQL so can
someone explain to me what's going on?

On 26/09/2008 14:35, Chris Baechle wrote:

> CREATE OR REPLACE FUNCTION user_checkCredentials(character varying)
> RETURNS character varying AS
> $BODY$
> DECLARE
>      username ALIAS FOR $1;
>      permission record;
> BEGIN
>     select into permission permtype from users;
>     RETURN permission.permtype;

You're missing the "where" clause from the query - it should be:

  select into permission permtype from users
    where uid = username;

...where uid is the name of the appropriate column in your users table.

Ray.

------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------

"Chris Baechle" <bangular@gmail.com> writes:
> When I try to run it with:
> select user_checkCredentials("asdf");

> I get the error:
> ERROR:  column "asdf" does not exist

You're confused about single quotes (literal strings) versus double
quotes (identifiers).

            regards, tom lane

On 26/09/2008 14:41, Raymond O'Donnell wrote:
> CREATE OR REPLACE FUNCTION user_checkCredentials(character varying)
> RETURNS character varying AS
> $BODY$
> DECLARE
>      username ALIAS FOR $1;

I meant to say too that in pl/pgsql, you can use argument names directly
(unless you're using a *really* old version of PostgreSQL), making your
code easier to read:

create function user_checkCredentials(username character varying)
returns character varying as
$$
declare
  permission character varying;
begin
  select into permission permtype from users
    where uid = username;
  return permtype;
end
$$
language plpgsql;

I'd also use "character varying" for "permtype"; there's no need to use
a record as you're only only getting a single column.

Ray.

------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------

On 26/09/2008 14:52, Raymond O'Donnell wrote:
> begin
>   select into permission permtype from users
>     where uid = username;
>   return permtype;
> end

Whoops - that should be

    return permission;

That's enough good advice from me for today.... :-)

Ray.

------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------

On Fri, 26 Sep 2008, Chris Baechle wrote:

> When I try to run it with:
> select user_checkCredentials("asdf");

Actually, I think the function probably isn't at fault here, string
literals should be surrounded with ' not ".

On 26/09/2008 14:40, Stephan Szabo wrote:

> Actually, I think the function probably isn't at fault here, string
> literals should be surrounded with ' not ".

Yes, that's true. In addition to that, however, if you look at your
SELECT statement you're selecting all users in the table; the argument
passed to the function isn't used anywhere.

In fairness, you did say that what you posted was a shortened version of
your real function, so maybe something got lost in the shortening...

Ray.

------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------