Skip site navigation (1) Skip section navigation (2)

Re: stored procedures in postgresql user plpgsql

From: Michael Fuhr <mike(at)fuhr(dot)org>
To: RobertD(dot)Stewart(at)ky(dot)gov
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: stored procedures in postgresql user plpgsql
Date: 2004-11-29 22:25:50
Message-ID: 20041129222550.GA67787@winnie.fuhr.org (view raw or flat)
Thread:
Lists: pgsql-sql
On Mon, Nov 29, 2004 at 01:37:42PM -0500, RobertD(dot)Stewart(at)ky(dot)gov wrote:

> I have tried to create a simple function to select data from a table.  Could
> you all please correct me

It would be helpful if you told us what you want to happen and what
actually does happen.  Without that information we have to guess
at your intentions.

>   CREATE FUNCTION retrive_atype(varchar) RETURNS varchar AS '
>         BEGIN
>             RETURN select username from masteraccount where atype=$1;
>         END;
>     ' LANGUAGE 'plpgsql';
> 
> To call the function I used 
> 
> Select retrive_atype();

You've defined the function to take a VARCHAR argument but you
called it without an argument.  Also, you've defined it to return
a single VARCHAR value, but if the query could return multiple
values then the function should return SETOF VARCHAR.  Maybe this
is closer to what you need:

CREATE FUNCTION retrive_atype(VARCHAR) RETURNS SETOF VARCHAR AS '
DECLARE
    rec  RECORD;
BEGIN
    FOR rec IN SELECT username FROM masteraccount WHERE atype = $1 LOOP
        RETURN NEXT rec.username;
    END LOOP;

    RETURN;
END;
' LANGUAGE plpgsql;

You'd call the function like this:

SELECT * FROM retrive_atype('some-atype-value');

If that's not what you're looking for then please provide more details.

BTW, is "retrive" supposed to be "retrieve"?

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/

In response to

pgsql-sql by date

Next:From: NurdinDate: 2004-11-30 02:57:12
Subject: create stored procedure from temporary table
Previous:From: Johan HenselmansDate: 2004-11-29 20:26:13
Subject: grouping a many to many relation set

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group