pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedstored procedures in postgresql user plpgsql
3+ messages / 2 participants
[nested] [flat]
* stored procedures in postgresql user plpgsql
@ 2004-11-29 18:37 RobertD.Stewart@ky.gov
2004-11-29 22:25 ` Re: stored procedures in postgresql user plpgsql Michael Fuhr <mike@fuhr.org>
0 siblings, 1 reply; 3+ messages in thread
From: RobertD.Stewart@ky.gov @ 2004-11-29 18:37 UTC (permalink / raw)
To: pgsql-sql
I know that there are not stored procedures like that in oracle. I know that
you have to create functions.
I have tried to create a simple function to select data from a table. Could
you all please correct me
Thanks
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();
Please help
Robert Stewart
Network Eng
Commonwealth Office of Technology
Finance and Administration Cabinet
101 Cold Harbor
Work # 502 564 9696
Cell # 502 330 5991
Email RobertD.Stewart@ky.gov <mailto:RobertD.Stewart@ky.gov>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: stored procedures in postgresql user plpgsql
2004-11-29 18:37 stored procedures in postgresql user plpgsql RobertD.Stewart@ky.gov
@ 2004-11-29 22:25 ` Michael Fuhr <mike@fuhr.org>
0 siblings, 0 replies; 3+ messages in thread
From: Michael Fuhr @ 2004-11-29 22:25 UTC (permalink / raw)
To: RobertD.Stewart@ky.gov; +Cc: pgsql-sql
On Mon, Nov 29, 2004 at 01:37:42PM -0500, RobertD.Stewart@ky.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/
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: stored procedures in postgresql user plpgsql
@ 2004-11-30 04:56 Michael Fuhr <mike@fuhr.org>
0 siblings, 0 replies; 3+ messages in thread
From: Michael Fuhr @ 2004-11-30 04:56 UTC (permalink / raw)
To: RobertD.Stewart@ky.gov; +Cc: pgsql-sql
Please Cc the mailing list when you respond -- that way others can
participate in and learn from the discussion. Also, if you have a
new question then please start a new thread with a relevant Subject
header.
On Mon, Nov 29, 2004 at 10:12:59PM -0500, RobertD.Stewart@ky.gov wrote:
> Thanks for the help
>
> That is what I needed
>
> Do you have any examples of insert statements for multiple variable
>
> I'm trying to create a web page that would call these functions
See the documentation for the INSERT statement:
http://www.postgresql.org/docs/7.4/static/sql-insert.html
If you're having trouble with something, then please describe what
you're trying to do and the difficulties you're having. It's helpful
if you post the SQL statements you're executing along with a description
of what you want to happen and what actually does happen. For the
latter, including the output from a psql session can be useful.
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2004-11-30 04:56 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-11-29 18:37 stored procedures in postgresql user plpgsql RobertD.Stewart@ky.gov
2004-11-29 22:25 ` Michael Fuhr <mike@fuhr.org>
2004-11-30 04:56 Re: stored procedures in postgresql user plpgsql Michael Fuhr <mike@fuhr.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox