agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedStored Procedures..
23+ messages / 16 participants
[nested] [flat]
* Stored Procedures..
@ 2000-03-31 22:47 S Kalyanasundaram <ranjani@niksun.com>
0 siblings, 0 replies; 23+ messages in thread
From: S Kalyanasundaram @ 2000-03-31 22:47 UTC (permalink / raw)
To: pgsql-sql@postgresql.org, Bruce Momjian <pgman@candle.pha.pa.us>
Hello
how do i call a stored procedure( CREATE FUNCTION...) written
in PL/PgSQL from a C++ program.. ?
Thanks in advance..
ranjani.
^ permalink raw reply [nested|flat] 23+ messages in thread
* Stored Procedures?
@ 2001-05-24 12:08 Chris Ruprecht <chrup999@yahoo.com>
0 siblings, 1 reply; 23+ messages in thread
From: Chris Ruprecht @ 2001-05-24 12:08 UTC (permalink / raw)
To: pgsql-sql
I read something about stored procedures in the Great Bridge User's Manual
(Page 74 under "PG_LANGUAGE"). It is only mentioned briefly and there are no
explanations of how it works.
Can anybody let me know, how I can write a stored procedure and how to run
it?
Best regards,
Chris
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Stored Procedures?
@ 2001-05-24 13:39 Tod McQuillin <devin@spamcop.net>
parent: Chris Ruprecht <chrup999@yahoo.com>
0 siblings, 1 reply; 23+ messages in thread
From: Tod McQuillin @ 2001-05-24 13:39 UTC (permalink / raw)
To: Chris Ruprecht <chrup999@yahoo.com>; +Cc: pgsql-sql
> I read something about stored procedures in the Great Bridge User's Manual
> (Page 74 under "PG_LANGUAGE"). It is only mentioned briefly and there are no
> explanations of how it works.
>
> Can anybody let me know, how I can write a stored procedure and how to run
> it?
Postgres doesn't have stored procedures in the same way that other
databases like oracle and sybase do. But it does have stored functions,
and they can be used in almost exactly the same way.
You create a function like this:
CREATE FUNCTION get_country(text) RETURNS text AS '
DECLARE
country_name country.name%TYPE;
country_key country.key%TYPE;
country_rec RECORD;
BEGIN
IF $1 ISNULL THEN
RETURN NULL;
END IF;
country_name = initcap($1);
SELECT INTO country_rec * FROM country
WHERE name = country_name;
IF FOUND THEN
RETURN country_rec.key;
END IF;
country_key := nextval(''country_key_seq'');
INSERT INTO country
VALUES (country_key, country_name);
RETURN country_key;
END;
' LANGUAGE 'plpgsql';
(You will need to load plpgsql support into your database. See the
createlang command for details.)
And you call it with SELECT, like this:
SELECT get_country('Zimbabwe');
Or from INSERT, like this:
INSERT INTO person (name, country_key)
VALUES ('Fred', get_country('Japan'));
The only difference between a function and a procedure is that a function
returns a value. If you don't need to return a value just pick a random
small result type (like bool, or int) return NULL, and ignore the return
value.
Usually I return a value even from procedural functions though just to
indicate if things went ok or not.
--
Tod McQuillin
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Stored Procedures?
@ 2001-05-24 22:54 Reinoud van Leeuwen <reinoud@xs4all.nl>
parent: Tod McQuillin <devin@spamcop.net>
0 siblings, 0 replies; 23+ messages in thread
From: reinoud@xs4all.nl @ 2001-05-24 22:54 UTC (permalink / raw)
To: pgsql-sql
On Thu, 24 May 2001 13:45:46 +0000 (UTC), devin@spamcop.net (Tod
McQuillin) wrote:
>Postgres doesn't have stored procedures in the same way that other
>databases like oracle and sybase do. But it does have stored functions,
>and they can be used in almost exactly the same way.
In Sybase I am used to the fact that stored procedures can return a
result set to the client. Something like;
create procedure sp_example (@param int) as
begin
select bla
from tablename
where somecolumn = @param
end
(of course in pratice stored procedures get a lot more complex than
this :-)
Is something like this posstible in PostgreSQL?
--
__________________________________________________
"Nothing is as subjective as reality"
Reinoud van Leeuwen reinoud@xs4all.nl
http://www.xs4all.nl/~reinoud
__________________________________________________
^ permalink raw reply [nested|flat] 23+ messages in thread
* Stored Procedures
@ 2002-10-01 18:16 bcschnei@attbi.com
0 siblings, 1 reply; 23+ messages in thread
From: bcschnei@attbi.com @ 2002-10-01 18:16 UTC (permalink / raw)
To: pgsql-sql
Hi all. I'm looking for a little help here. I have a
project where I have to write some stored proceedures
and am having some problems. My main issue is, I cannot
figure out how to return a record set containing
multipule columns. I am looking for a few examples on
how I can do this. Most of what I have to do is fairly
simple SQL queries based on a pramater sent to the
function. I tried to use the SETOF <datatype> option,
but only get back one column.
Any help will be would be greatly appricated. Simple
examples would be of a great help.
Thanks,
Ben
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Stored Procedures
@ 2002-10-01 19:08 Roberto Mello <rmello@cc.usu.edu>
parent: bcschnei@attbi.com
0 siblings, 0 replies; 23+ messages in thread
From: Roberto Mello @ 2002-10-01 19:08 UTC (permalink / raw)
To: bcschnei@attbi.com
On Tue, Oct 01, 2002 at 06:16:57PM +0000, bcschnei@attbi.com wrote:
> Hi all. I'm looking for a little help here. I have a
> project where I have to write some stored proceedures
> and am having some problems. My main issue is, I cannot
> figure out how to return a record set containing
> multipule columns. I am looking for a few examples on
> how I can do this. Most of what I have to do is fairly
> simple SQL queries based on a pramater sent to the
> function. I tried to use the SETOF <datatype> option,
> but only get back one column.
In 7.2 this is acomplished through returning a cursor from the function.
See the 7.3 documentation to see how to do that (AFAIK, this is not
documented in the 7.2 docs, although it does work).
In 7.3 you can return true record sets without the use of cursors. Again,
see the docs for 7.3 in the developers site.
-Roberto
--
+----| Roberto Mello - http://www.brasileiro.net/ |------+
+ Computer Science Graduate Student, Utah State University +
+ USU Free Software & GNU/Linux Club - http://fslc.usu.edu/ +
Q: How many IBM CPU's does it take to do a logical right shift?
A: 33. 1 to hold the bits and 32 to push the register.
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Stored Procedures
@ 2002-10-02 15:47 david williams <dw_remote@hotmail.com>
0 siblings, 0 replies; 23+ messages in thread
From: david williams @ 2002-10-02 15:47 UTC (permalink / raw)
To: bcschnei@attbi.com; pgsql-sql
http://developer.postgresql.org/docs/postgres/xfunc-sql.html#AEN30400
See section
9.2.4. SQL Table Functions
----- Original Message -----
From: bcschnei@attbi.com
Sent: Tuesday, October 01, 2002 4:25 PM
To: pgsql-sql@postgresql.org
Subject: [SQL] Stored Procedures
Hi all. I'm looking for a little help here. I have a
project where I have to write some stored proceedures
and am having some problems. My main issue is, I cannot
figure out how to return a record set containing
multipule columns. I am looking for a few examples on
how I can do this. Most of what I have to do is fairly
simple SQL queries based on a pramater sent to the
function. I tried to use the SETOF <datatype> option,
but only get back one column.
Any help will be would be greatly appricated. Simple
examples would be of a great help.
Thanks,
Ben
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.orgGet more from the Web. FREE MSN Explorer download : http://explorer.msn.com
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Stored Procedures
@ 2002-10-02 15:50 david williams <dw_remote@hotmail.com>
0 siblings, 1 reply; 23+ messages in thread
From: david williams @ 2002-10-02 15:50 UTC (permalink / raw)
To: bcschnei@attbi.com; pgsql-sql
Also,
the table definition MUST be in the Public Schema. I use my own schema names but in order for the table to be found by the function it ( the table ) must be in the public schema. Although it can be empty.
DaveGet more from the Web. FREE MSN Explorer download : http://explorer.msn.com
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Stored Procedures
@ 2002-10-02 16:34 Joe Conway <mail@joeconway.com>
parent: david williams <dw_remote@hotmail.com>
0 siblings, 0 replies; 23+ messages in thread
From: Joe Conway @ 2002-10-02 16:34 UTC (permalink / raw)
To: david williams <dw_remote@hotmail.com>; +Cc: bcschnei@attbi.com; pgsql-sql
david williams wrote:
> Also,
>
> the table definition MUST be in the Public Schema. I use my own schema
> names but in order for the table to be found by the function it ( the
> table ) must be in the public schema. Although it can be empty.
(Note:
this discussion does not apply to PostgreSQL releases prior to 7.3 beta)
Not true. You need to be sure the schema the table is in is in your search
path, or you need to fully qualify the table reference. See below for an example:
-- create a new schema
CREATE SCHEMA s1;
CREATE SCHEMA
-- change to the new schema
SET search_path='s1','$user','public';
SET
select current_schema();
current_schema
----------------
s1
(1 row)
-- create the table
CREATE TABLE foo (fooid int, foosubid int, fooname text);
CREATE TABLE
INSERT INTO foo VALUES(1,1,'Joe');
INSERT 794076 1
-- change back to public schema, but leave s1 in the search path
SET search_path='$user','public','s1';
SET
select current_schema();
current_schema
----------------
public
(1 row)
\dt
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
s1 | foo | table | postgres
(1 row)
CREATE FUNCTION getfoo(int) RETURNS foo AS '
SELECT * FROM foo WHERE fooid = $1;
' LANGUAGE SQL;
CREATE FUNCTION
\df getfoo
List of functions
Result data type | Schema | Name | Argument data types
------------------+--------+--------+---------------------
foo | public | getfoo | integer
(1 row)
-- this will work
SELECT *, upper(fooname) FROM getfoo(1) AS t1;
fooid | foosubid | fooname | upper
-------+----------+---------+-------
1 | 1 | Joe | JOE
(1 row)
-- now try again with table name qualified in the function
DROP FUNCTION getfoo(int);
DROP FUNCTION
-- remove s1 from the search path
SET search_path='$user','public';
SET
select current_schema();
current_schema
----------------
public
(1 row)
\dt
No relations found.
CREATE FUNCTION getfoo(int) RETURNS s1.foo AS '
SELECT * FROM s1.foo WHERE fooid = $1;
' LANGUAGE SQL;
CREATE FUNCTION
\df getfoo
List of functions
Result data type | Schema | Name | Argument data types
------------------+--------+--------+---------------------
s1.foo | public | getfoo | integer
(1 row)
-- this will work
SELECT *, upper(fooname) FROM getfoo(1) AS t1;
fooid | foosubid | fooname | upper
-------+----------+---------+-------
1 | 1 | Joe | JOE
(1 row)
HTH,
Joe
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Stored Procedures
@ 2002-10-02 17:53 bcschnei@attbi.com
0 siblings, 1 reply; 23+ messages in thread
From: bcschnei@attbi.com @ 2002-10-02 17:53 UTC (permalink / raw)
To: Joe Conway <mail@joeconway.com>; +Cc: david williams <dw_remote@hotmail.com>; pgsql-sql
Ok, if this does not apply to versions prior to 7.3beta
then what do I need to do if I am running 7.2.1? When I
try to use the SETOF to retrun a row set, I only get
one column.
Do I need to update Postgres to get things to work?
Ben
> david williams wrote:
> > Also,
> >
> > the table definition MUST be in the Public Schema. I use my own schema
> > names but in order for the table to be found by the function it ( the
> > table ) must be in the public schema. Although it can be empty.
>
> (Note:
> this discussion does not apply to PostgreSQL releases prior to 7.3 beta)
>
> Not true. You need to be sure the schema the table is in is in your search
> path, or you need to fully qualify the table reference. See below for an
> example:
>
> -- create a new schema
> CREATE SCHEMA s1;
> CREATE SCHEMA
> -- change to the new schema
> SET search_path='s1','$user','public';
> SET
> select current_schema();
> current_schema
> ----------------
> s1
> (1 row)
>
> -- create the table
> CREATE TABLE foo (fooid int, foosubid int, fooname text);
> CREATE TABLE
> INSERT INTO foo VALUES(1,1,'Joe');
> INSERT 794076 1
> -- change back to public schema, but leave s1 in the search path
> SET search_path='$user','public','s1';
> SET
> select current_schema();
> current_schema
> ----------------
> public
> (1 row)
>
> \dt
> List of relations
> Schema | Name | Type | Owner
> --------+------+-------+----------
> s1 | foo | table | postgres
> (1 row)
>
> CREATE FUNCTION getfoo(int) RETURNS foo AS '
> SELECT * FROM foo WHERE fooid = $1;
> ' LANGUAGE SQL;
> CREATE FUNCTION
> \df getfoo
> List of functions
> Result data type | Schema | Name | Argument data types
> ------------------+--------+--------+---------------------
> foo | public | getfoo | integer
> (1 row)
>
> -- this will work
> SELECT *, upper(fooname) FROM getfoo(1) AS t1;
> fooid | foosubid | fooname | upper
> -------+----------+---------+-------
> 1 | 1 | Joe | JOE
> (1 row)
>
> -- now try again with table name qualified in the function
> DROP FUNCTION getfoo(int);
> DROP FUNCTION
> -- remove s1 from the search path
> SET search_path='$user','public';
> SET
> select current_schema();
> current_schema
> ----------------
> public
> (1 row)
>
> \dt
> No relations found.
> CREATE FUNCTION getfoo(int) RETURNS s1.foo AS '
> SELECT * FROM s1.foo WHERE fooid = $1;
> ' LANGUAGE SQL;
> CREATE FUNCTION
> \df getfoo
> List of functions
> Result data type | Schema | Name | Argument data types
> ------------------+--------+--------+---------------------
> s1.foo | public | getfoo | integer
> (1 row)
>
> -- this will work
> SELECT *, upper(fooname) FROM getfoo(1) AS t1;
> fooid | foosubid | fooname | upper
> -------+----------+---------+-------
> 1 | 1 | Joe | JOE
> (1 row)
>
> HTH,
>
> Joe
>
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Stored Procedures
@ 2002-10-02 17:57 david williams <dw_remote@hotmail.com>
0 siblings, 0 replies; 23+ messages in thread
From: david williams @ 2002-10-02 17:57 UTC (permalink / raw)
To: bcschnei@attbi.com; Joe Conway <mail@joeconway.com>; +Cc: pgsql-sql
Stored procedures returning more than one row up through odbc does not work in 7.2.1
To return more than one column you must spec is column in the returns area of the function.
Dave
----- Original Message -----
From: bcschnei@attbi.com
Sent: Wednesday, October 02, 2002 1:53 PM
To: Joe Conway
Cc: david williams; pgsql-sql@postgresql.org
Subject: Re: [SQL] Stored Procedures
Ok, if this does not apply to versions prior to 7.3beta
then what do I need to do if I am running 7.2.1? When I
try to use the SETOF to retrun a row set, I only get
one column.
Do I need to update Postgres to get things to work?
Ben
> david williams wrote:
> > Also,
> >
> > the table definition MUST be in the Public Schema. I use my own schema
> > names but in order for the table to be found by the function it ( the
> > table ) must be in the public schema. Although it can be empty.
>
> (Note:
> this discussion does not apply to PostgreSQL releases prior to 7.3 beta)
>
> Not true. You need to be sure the schema the table is in is in your search
> path, or you need to fully qualify the table reference. See below for an
> example:
>
> -- create a new schema
> CREATE SCHEMA s1;
> CREATE SCHEMA
> -- change to the new schema
> SET search_path='s1','$user','public';
> SET
> select current_schema();
> current_schema
> ----------------
> s1
> (1 row)
>
> -- create the table
> CREATE TABLE foo (fooid int, foosubid int, fooname text);
> CREATE TABLE
> INSERT INTO foo VALUES(1,1,'Joe');
> INSERT 794076 1
> -- change back to public schema, but leave s1 in the search path
> SET search_path='$user','public','s1';
> SET
> select current_schema();
> current_schema
> ----------------
> public
> (1 row)
>
> \dt
> List of relations
> Schema | Name | Type | Owner
> --------+------+-------+----------
> s1 | foo | table | postgres
> (1 row)
>
> CREATE FUNCTION getfoo(int) RETURNS foo AS '
> SELECT * FROM foo WHERE fooid = $1;
> ' LANGUAGE SQL;
> CREATE FUNCTION
> \df getfoo
> List of functions
> Result data type | Schema | Name | Argument data types
> ------------------+--------+--------+---------------------
> foo | public | getfoo | integer
> (1 row)
>
> -- this will work
> SELECT *, upper(fooname) FROM getfoo(1) AS t1;
> fooid | foosubid | fooname | upper
> -------+----------+---------+-------
> 1 | 1 | Joe | JOE
> (1 row)
>
> -- now try again with table name qualified in the function
> DROP FUNCTION getfoo(int);
> DROP FUNCTION
> -- remove s1 from the search path
> SET search_path='$user','public';
> SET
> select current_schema();
> current_schema
> ----------------
> public
> (1 row)
>
> \dt
> No relations found.
> CREATE FUNCTION getfoo(int) RETURNS s1.foo AS '
> SELECT * FROM s1.foo WHERE fooid = $1;
> ' LANGUAGE SQL;
> CREATE FUNCTION
> \df getfoo
> List of functions
> Result data type | Schema | Name | Argument data types
> ------------------+--------+--------+---------------------
> s1.foo | public | getfoo | integer
> (1 row)
>
> -- this will work
> SELECT *, upper(fooname) FROM getfoo(1) AS t1;
> fooid | foosubid | fooname | upper
> -------+----------+---------+-------
> 1 | 1 | Joe | JOE
> (1 row)
>
> HTH,
>
> Joe
> Get more from the Web. FREE MSN Explorer download : http://explorer.msn.com
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Stored Procedures
@ 2002-10-02 18:09 Joe Conway <mail@joeconway.com>
parent: bcschnei@attbi.com
0 siblings, 0 replies; 23+ messages in thread
From: Joe Conway @ 2002-10-02 18:09 UTC (permalink / raw)
To: bcschnei@attbi.com; +Cc: david williams <dw_remote@hotmail.com>; pgsql-sql
bcschnei@attbi.com wrote:
> Ok, if this does not apply to versions prior to 7.3beta
> then what do I need to do if I am running 7.2.1? When I
> try to use the SETOF to retrun a row set, I only get
> one column.
First, prior to 7.3 there is no SCHEMA support in Postgres. Everything lives
in essentially one and the same schema.
In 7.2.x and before, returning a composite type (i.e. multiple columns) gives
you back one column of pointers (large integer values) to the actual row of
data. You can access the individual columns, but it's ugly:
test=# CREATE TABLE foo (fooid int, foosubid int, fooname text);
CREATE
test=# INSERT INTO foo VALUES(1,1,'Joe');
INSERT 304822 1
test=# CREATE FUNCTION getfoo(int) RETURNS foo AS '
test'# SELECT * FROM foo WHERE fooid = $1;
test'# ' LANGUAGE SQL;
CREATE
test=# select fooid(getfoo(1)), foosubid(getfoo(1)), fooname(getfoo(1));
fooid | foosubid | fooname
-------+----------+---------
1 | 1 | Joe
(1 row)
Joe
^ permalink raw reply [nested|flat] 23+ messages in thread
* Stored procedures
@ 2003-03-28 22:31 Zodiac <bishop@nm.ru>
0 siblings, 1 reply; 23+ messages in thread
From: Zodiac @ 2003-03-28 22:31 UTC (permalink / raw)
To: pgsql-sql
Hello!
Can anybody tell me one thing.
How can i call stored procedures in my java-programm?
Thanks for any help.
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Stored procedures
@ 2003-03-28 23:40 Franco Bruno Borghesi <franco@akyasociados.com.ar>
parent: Zodiac <bishop@nm.ru>
0 siblings, 1 reply; 23+ messages in thread
From: Franco Bruno Borghesi @ 2003-03-28 23:40 UTC (permalink / raw)
To: Zodiac <bishop@nm.ru>; pgsql-sql
Here is a full example of a java program showing the data from a set returning
function:
-------------------------
--IN YOUR DATABASE
CREATE TABLE people (name TEXT);
INSERT INTO people VALUES ('john');
INSERT INTO people VALUES ('peter');
INSERT INTO people VALUES ('joe');
CREATE FUNCTION getPeople() RETURNS SETOF people AS '
DECLARE
rec RECORD;
BEGIN
FOR rec IN
SELECT name FROM people
LOOP
RETURN NEXT rec;
END LOOP;
RETURN;
END;' LANGUAGE 'plpgsql';
-------------------
--ListPeople.java
import java.sql.*;
public class ListPeople {
public static void main(String[] args) {
try {
Class.forName("org.postgresql.Driver");
Connection
con=DriverManager.getConnection("jdbc:postgresql:franco?user=admin");
Statement stmt=con.createStatement();
ResultSet rs=stmt.executeQuery("SELECT * FROM getPeople()");
while (rs.next()) {
System.out.println(rs.getString("name"));
}
}
catch (Exception e) {
System.out.println("Exception: "+e.getMessage());
}
}
}
On Friday 28 March 2003 19:31, Zodiac wrote:
> Hello!
> Can anybody tell me one thing.
> How can i call stored procedures in my java-programm?
>
> Thanks for any help.
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Stored procedures
@ 2003-03-29 20:35 Zodiac <bishop@nm.ru>
parent: Franco Bruno Borghesi <franco@akyasociados.com.ar>
0 siblings, 1 reply; 23+ messages in thread
From: Zodiac @ 2003-03-29 20:35 UTC (permalink / raw)
To: Franco Bruno Borghesi <franco@akyasociados.com.ar>; +Cc: pgsql-sql
Thank you for help.
Just one more question. Have i direct access to stored procedure?
For example, i have procedure which returns Integer and i wanna to have
ability to write such code " int var = ANY_CALL". Where ANY_CALL is a my
procedure call.
I meant must i do "executeQuery" only and after then parse Statement
variable?
Thank you.
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Stored procedures
@ 2003-03-29 21:05 Franco Bruno Borghesi <franco@akyasociados.com.ar>
parent: Zodiac <bishop@nm.ru>
0 siblings, 0 replies; 23+ messages in thread
From: Franco Bruno Borghesi @ 2003-03-29 21:05 UTC (permalink / raw)
To: Zodiac <bishop@nm.ru>; +Cc: pgsql-sql
As far as I know, you always work with a ResultSet.
If you know your stored procedures will always return an Integer and you don't
wanna deal with the executeQuery and stuff every time, you could create a
class with methods explicitly for accesing your stored procedures, for
example:
assuming you have a pg function returning an INT, called countPeople(), you
could do
public class MyStoredProcs {
private static int executeAnyProc(Connection conn, String procName) throws
SQLException{
Statement stmt=conn.createStatement();
ResultSet rs=stmt.executeQuery("SELECT * FROM "+procName+"()");
rs.next();
return rs.getInt(1);
}
public static int countPeople() throws SQLException{
return executeAnyProc("countPeople");
}
};
You could add methods to access every stored procedure in your database (even
returning other data types), and you would use it like this in your code:
...
int count=MyStoredProcs.countPeople();
// do something with the value
if (count>100) {
...
hope this is what you were looking for.
On Saturday 29 March 2003 17:35, Zodiac wrote:
> Thank you for help.
> Just one more question. Have i direct access to stored procedure?
> For example, i have procedure which returns Integer and i wanna to have
> ability to write such code " int var = ANY_CALL". Where ANY_CALL is a my
> procedure call.
> I meant must i do "executeQuery" only and after then parse Statement
> variable?
>
> Thank you.
^ permalink raw reply [nested|flat] 23+ messages in thread
* Stored Procedures
@ 2003-12-29 01:57 beyaRecords - The home Urban music <uzo@beya-records.com>
0 siblings, 2 replies; 23+ messages in thread
From: beyaRecords - The home Urban music @ 2003-12-29 01:57 UTC (permalink / raw)
To: pgsql-sql
Hi,
I am new to this list and the world of postgreSQL, and would like to
know how create stored procedures in postgreSQL.
regards
Uzo
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Stored Procedures
@ 2003-12-29 02:02 Christopher Browne <cbbrowne@acm.org>
parent: beyaRecords - The home Urban music <uzo@beya-records.com>
1 sibling, 0 replies; 23+ messages in thread
From: Christopher Browne @ 2003-12-29 02:02 UTC (permalink / raw)
To: pgsql-sql
uzo@beya-records.com (beyaRecords - The home Urban music) wrote:
> I am new to this list and the world of postgreSQL, and would like to
> know how create stored procedures in postgreSQL.
Have you considered looking at the documentation?
It is fairly well documented there.
% man "CREATE FUNCTION"
is quite likely to provide you with the documentation you require.
--
If this was helpful, <http://svcs.affero.net/rm.php?r=cbbrowne; rate me
http://www3.sympatico.ca/cbbrowne/postgresql.html
"We have no need to punish Pascal programmers. Pascal programming,
like chastity, is its own punishment. The only way I could imagine to
make their wretched state any worse would be to make them use Ada."
-- Scott Fahlman
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Stored Procedures
@ 2003-12-29 02:15 Michael Fuhr <mike@fuhr.org>
parent: beyaRecords - The home Urban music <uzo@beya-records.com>
1 sibling, 0 replies; 23+ messages in thread
From: Michael Fuhr @ 2003-12-29 02:15 UTC (permalink / raw)
To: beyaRecords - The home Urban music <uzo@beya-records.com>; +Cc: pgsql-sql
On Mon, Dec 29, 2003 at 01:57:00AM +0000, beyaRecords - The home Urban music wrote:
>
> I am new to this list and the world of postgreSQL, and would like to
> know how create stored procedures in postgreSQL.
If you're new to PostgreSQL then have a look at the documentation:
http://www.postgresql.org/docs/
To learn about stored procedures, see the "Server Programming" part,
especially the "Extending SQL" and "PL/pgSQL - SQL Procedural Language"
chapters:
http://www.postgresql.org/docs/current/static/server-programming.html
http://www.postgresql.org/docs/current/static/extend.html
http://www.postgresql.org/docs/current/static/plpgsql.html
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
^ permalink raw reply [nested|flat] 23+ messages in thread
* Stored procedures
@ 2004-01-02 01:50 beyaRecords - The home Urban music <uzo@beya-records.com>
0 siblings, 1 reply; 23+ messages in thread
From: beyaRecords - The home Urban music @ 2004-01-02 01:50 UTC (permalink / raw)
To: pgsql-sql
Hi,
I am having problems with a stored procedure (plpgsql) that takes in a
value and returns a record set.
my code is as follow:
create function pg_clientRec(text) setof record as
'
declare
customerID ALIAS $1;
rec record;
begin
select into rec * from troubletickets where custID = customerID;
return rec;
end
'
language 'plpgsql';
I am calling the procedure as follows:
select clientRec('tmpg60');
I am getting the following error:
ERROR: set-valued function called in context that cannot accept a set
CONTEXT: PL/
pgSQL function "pg_clientRec" while casting return value to function's r
eturn type
What am I doing wrong????
regards
Uzo
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Stored procedures
@ 2004-01-02 02:10 Tom Lane <tgl@sss.pgh.pa.us>
parent: beyaRecords - The home Urban music <uzo@beya-records.com>
0 siblings, 0 replies; 23+ messages in thread
From: Tom Lane @ 2004-01-02 02:10 UTC (permalink / raw)
To: beyaRecords - The home Urban music <uzo@beya-records.com>; +Cc: pgsql-sql
beyaRecords - The home Urban music <uzo@beya-records.com> writes:
> I am having problems with a stored procedure (plpgsql) that takes in a
> value and returns a record set.
> I am calling the procedure as follows:
> select clientRec('tmpg60');
Use
select * from clientRec('tmpg60') as (column list);
If you declare the function as returning RECORD, you will need to
provide an AS clause that identifies the column set the records will
contain. Without this, the parser has no idea what to expand "*" to.
See the example in section 7.2.1.4 here:
http://www.postgresql.org/docs/7.4/static/queries-table-expressions.html
regards, tom lane
^ permalink raw reply [nested|flat] 23+ messages in thread
* stored procedures
@ 2023-10-17 15:52 Shaozhong SHI <shishaozhong@gmail.com>
0 siblings, 1 reply; 23+ messages in thread
From: Shaozhong SHI @ 2023-10-17 15:52 UTC (permalink / raw)
To: pgsql-sql <pgsql-sql@lists.postgresql.org>
How easily turn do statement scripts into stored procedures? Much
modification is needed?
What is the advantage to do so?
Regards,
David
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: stored procedures
@ 2023-10-17 16:04 David G. Johnston <david.g.johnston@gmail.com>
parent: Shaozhong SHI <shishaozhong@gmail.com>
0 siblings, 0 replies; 23+ messages in thread
From: David G. Johnston @ 2023-10-17 16:04 UTC (permalink / raw)
To: Shaozhong SHI <shishaozhong@gmail.com>; +Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
On Tue, Oct 17, 2023 at 8:54 AM Shaozhong SHI <shishaozhong@gmail.com>
wrote:
> How easily turn do statement scripts into stored procedures? Much
> modification is needed?
>
> What is the advantage to do so?
>
Depends on the script but possibly a matter of copy-paste for the simplest.
Advantages: The script is on the server, can require permissions, has a
name (I suppose a script file does too...). Easier to deal with input
parameters. Fewer components involved. Usable by components that can't
issue SQL directly - e.g., GraphQL mutations.
Disadvantages: Changing the script is now a formal migration for the
database instead of an application update.
David J.
^ permalink raw reply [nested|flat] 23+ messages in thread
end of thread, other threads:[~2023-10-17 16:04 UTC | newest]
Thread overview: 23+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-03-31 22:47 Stored Procedures.. S Kalyanasundaram <ranjani@niksun.com>
2001-05-24 12:08 Stored Procedures? Chris Ruprecht <chrup999@yahoo.com>
2001-05-24 13:39 ` Re: Stored Procedures? Tod McQuillin <devin@spamcop.net>
2001-05-24 22:54 ` Re: Stored Procedures? Reinoud van Leeuwen <reinoud@xs4all.nl>
2002-10-01 18:16 Stored Procedures bcschnei@attbi.com
2002-10-01 19:08 ` Re: Stored Procedures Roberto Mello <rmello@cc.usu.edu>
2002-10-02 15:47 Re: Stored Procedures david williams <dw_remote@hotmail.com>
2002-10-02 15:50 Re: Stored Procedures david williams <dw_remote@hotmail.com>
2002-10-02 16:34 ` Re: Stored Procedures Joe Conway <mail@joeconway.com>
2002-10-02 17:53 Re: Stored Procedures bcschnei@attbi.com
2002-10-02 18:09 ` Re: Stored Procedures Joe Conway <mail@joeconway.com>
2002-10-02 17:57 Re: Stored Procedures david williams <dw_remote@hotmail.com>
2003-03-28 22:31 Stored procedures Zodiac <bishop@nm.ru>
2003-03-28 23:40 ` Re: Stored procedures Franco Bruno Borghesi <franco@akyasociados.com.ar>
2003-03-29 20:35 ` Re: Stored procedures Zodiac <bishop@nm.ru>
2003-03-29 21:05 ` Re: Stored procedures Franco Bruno Borghesi <franco@akyasociados.com.ar>
2003-12-29 01:57 Stored Procedures beyaRecords - The home Urban music <uzo@beya-records.com>
2003-12-29 02:02 ` Re: Stored Procedures Christopher Browne <cbbrowne@acm.org>
2003-12-29 02:15 ` Re: Stored Procedures Michael Fuhr <mike@fuhr.org>
2004-01-02 01:50 Stored procedures beyaRecords - The home Urban music <uzo@beya-records.com>
2004-01-02 02:10 ` Re: Stored procedures Tom Lane <tgl@sss.pgh.pa.us>
2023-10-17 15:52 stored procedures Shaozhong SHI <shishaozhong@gmail.com>
2023-10-17 16:04 ` Re: stored procedures David G. Johnston <david.g.johnston@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox