[BUGS] BUG #3851: suggestion - support for stored procedures
The following bug has been logged online: Bug reference: 3851 Logged by: Lunter Email address: [EMAIL PROTECTED] PostgreSQL version: 9.0 ? Operating system: any Description:suggestion - support for stored procedures Details: Some database servers support stored procedures that return more than one rowset (also known as a result set). It is very usefull and it permit to full separate SQL statement from scripting language code and make possible return more than one rowset on one calling to database. CREATE PROC procedure_name [EMAIL PROTECTED] INT] AS BEGIN SELECT * FROM Table1 WHERE id = @var; SELECT * FROM Table2; SELECT * FROM Table3; END --- EXEC/CALL procedure_name [EMAIL PROTECTED] = 10] ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] BUG #3851: suggestion - support for stored procedures
Hello
use SETOF cursors.
CREATE FUNCTION myfunc(refcursor, refcursor) RETURNS SETOF refcursor AS $$
BEGIN
OPEN $1 FOR SELECT * FROM table_1;
RETURN NEXT $1;
OPEN $2 FOR SELECT * FROM table_2;
RETURN NEXT $2;
END;
$$ LANGUAGE plpgsql;
-- need to be in a transaction to use cursors.
BEGIN;
SELECT * FROM myfunc('a', 'b');
FETCH ALL FROM a;
FETCH ALL FROM b;
COMMIT;
http://www.postgresql.org/docs/8.2/interactive/plpgsql-cursors.html
Regards
Pavel Stehule
On 05/01/2008, Lunter <[EMAIL PROTECTED]> wrote:
>
> The following bug has been logged online:
>
> Bug reference: 3851
> Logged by: Lunter
> Email address: [EMAIL PROTECTED]
> PostgreSQL version: 9.0 ?
> Operating system: any
> Description:suggestion - support for stored procedures
> Details:
>
> Some database servers support stored procedures that return more than one
> rowset (also known as a result set).
> It is very usefull and it permit to full separate SQL statement from
> scripting language code and make possible return more than one rowset on one
> calling to database.
>
> CREATE PROC procedure_name
> [EMAIL PROTECTED] INT]
> AS
> BEGIN
> SELECT * FROM Table1 WHERE id = @var;
> SELECT * FROM Table2;
> SELECT * FROM Table3;
> END
>
> ---
>
> EXEC/CALL procedure_name [EMAIL PROTECTED] = 10]
>
> ---(end of broadcast)---
> TIP 4: Have you searched our list archives?
>
>http://archives.postgresql.org
>
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
[BUGS] pg_dump produces invalid SQL for "group by cast(null as numeric)"
Hi PostgreSQL developers, in [1], a user reported a failure of pg_dump: snip -- 1. Create an empty database. 2. Connect to the database and create these views: create view foo as select 3; create view bar as select count(*) from foo group by cast(null as numeric); 3. pg_dump the database to a text file. The file contains 'CREATE VIEW bar AS SELECT count(*) AS count FROM foo GROUP BY 2;' 4. Drop view bar from the database. 5. Run the CREATE VIEW bar..; statement from the text file. 6. The statement fails with 'ERROR: GROUP BY position 2 is not in select list' snip -- I verified that this is still an issue on 8.3 CVS head. However, I admit that I'm not sure why "group by cast(null as numeric)" is useful. However, it actually works in the DB and fails in pg_dump, so either it is valid and should be handled by pg_dump, or it is invalid and should not be accepted in the first place. Thank you! Martin [1] https://bugs.launchpad.net/bugs/177382 -- Martin Pitthttp://www.piware.de Ubuntu Developer http://www.ubuntu.com Debian Developer http://www.debian.org signature.asc Description: Digital signature
Re: [BUGS] pg_dump produces invalid SQL for "group by cast(null as numeric)"
Martin Pitt <[EMAIL PROTECTED]> writes:
> create view bar as select count(*) from foo group by cast(null as numeric);
> 3. pg_dump the database to a text file. The file contains
>'CREATE VIEW bar AS
>SELECT count(*) AS count FROM foo GROUP BY 2;'
Actually, this seems to be provoking an Assert failure, if you use
an assert-enabled backend:
$ pg_dump d1
pg_dump: SQL command failed
pg_dump: Error message from server: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
pg_dump: The command was: SELECT
pg_catalog.pg_get_viewdef('40965'::pg_catalog.oid) as viewdef
Server log shows
TRAP: FailedAssertion("!(!tle->resjunk)", File: "ruleutils.c", Line: 2267)
LOG: server process (PID 4507) was terminated by signal 6
So it's a backend problem not pg_dump's fault.
regards, tom lane
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
