Thread: Question about stored procedures
From what I understand, a stored procedure in pgSQL is actually a function that returns rows? Am I correct?
Can somebody show me the syntax for a basic stored procedure that takes an argument?
i.e I would like the query to be “select name, email from users where userid = @userid”
Sorry for the easy question but I looked at the docs and just can’t seem to find what I’m looking for…
am 12.06.2006, um 10:58:04 +0200 mailte Greg folgendes: > >From what I understand, a stored procedure in pgSQL is actually a function > that returns rows? Am I correct? Yes and no, if a function returns multiple rows, than is this a SRF (Set Returning Function) http://www.postgresql.org/docs/current/static/xfunc-sql.html#XFUNC-SQL-TABLE-FUNCTIONS http://www.varlena.com/GeneralBits/26 > > Can somebody show me the syntax for a basic stored procedure that takes an > argument? http://www.postgresql.org/docs/8.1/interactive/plpgsql-declarations.html there are examples. Read this. > i.e I would like the query to be "select name, email from users where userid > = @userid" If you want to execute dynamic querys, read http://www.postgresql.org/docs/8.1/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN > Sorry for the easy question but I looked at the docs and just can't seem to Read again. Andreas -- Andreas Kretschmer (Kontakt: siehe Header) Heynitz: 035242/47215, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net === Schollglas Unternehmensgruppe ===
On Mon, Jun 12, 2006 at 11:15:28AM +0200, A. Kretschmer wrote: > am 12.06.2006, um 10:58:04 +0200 mailte Greg folgendes: > > i.e I would like the query to be "select name, email from users where userid > > = @userid" > > If you want to execute dynamic querys, read > > http://www.postgresql.org/docs/8.1/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN The above query doesn't need to be dynamic if @userid is the only part that varies. Dynamic queries are appropriate when you want to vary the structure of the query itself, such as which columns are selected, which table gets selected from, which columns to test in the WHERE clause, which columns to order by, etc.; or if you want to defeat plan caching (e.g., when using temporary tables). -- Michael Fuhr
am 12.06.2006, um 6:23:48 -0600 mailte Michael Fuhr folgendes: > On Mon, Jun 12, 2006 at 11:15:28AM +0200, A. Kretschmer wrote: > > am 12.06.2006, um 10:58:04 +0200 mailte Greg folgendes: > > > i.e I would like the query to be "select name, email from users where userid > > > = @userid" > > > > If you want to execute dynamic querys, read > > > > http://www.postgresql.org/docs/8.1/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN > > The above query doesn't need to be dynamic if @userid is the only > part that varies. Dynamic queries are appropriate when you want Uhm, yes. Andreas -- Andreas Kretschmer (Kontakt: siehe Header) Heynitz: 035242/47215, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net === Schollglas Unternehmensgruppe ===