From: Bart Degryse <Bart.Degryse@indicator.be>
To: pgsql-sql@postgresql.org
Subject: Re: Sql ORDER BY and ASC/DESC question
Date: Wed, 30 Jan 2008 13:30:52 +0100
Message-ID: <47A07C0C.A3DD.0030.0@indicator.be> (raw)
In-Reply-To: <20080130104226.GF26490@a-kretschmer.de>
References: <aa457f86948348248e93c6fc38b4b1f6@pobox.sk>
<20080130104226.GF26490@a-kretschmer.de>
Actually there might be assuming your function is a set returning function.
This example eg works perfectly and sorts the output of the function without
having to use execute.
CREATE TABLE "public"."error_types" (
"id" SERIAL,
"errdesc" TEXT NOT NULL,
"autofix" BOOLEAN DEFAULT false NOT NULL,
CONSTRAINT "error_types_errdesc_key" UNIQUE("errdesc"),
CONSTRAINT "error_types_pkey" PRIMARY KEY("id")
) WITH (fillfactor = 100, OIDS = FALSE);
INSERT INTO "public"."error_types" ("errdesc", "autofix") VALUES ('Missing InvoiceID', True);
INSERT INTO "public"."error_types" ("errdesc", "autofix") VALUES ('Missing InventTransID', True);
INSERT INTO "public"."error_types" ("errdesc", "autofix") VALUES ('Illegal oneshot dates (start <> end)', False);
INSERT INTO "public"."error_types" ("errdesc", "autofix") VALUES ('Illegal dates (start > end)', False);
INSERT INTO "public"."error_types" ("errdesc", "autofix") VALUES ('Accountnumber not defined', False);
INSERT INTO "public"."error_types" ("errdesc", "autofix") VALUES ('Creditnote with a positive amount', False);
INSERT INTO "public"."error_types" ("errdesc", "autofix") VALUES ('Invoice with a negative amount', False);
INSERT INTO "public"."error_types" ("errdesc", "autofix") VALUES ('Original invoice not found', False);
INSERT INTO "public"."error_types" ("errdesc", "autofix") VALUES ('Update reknr', True);
INSERT INTO "public"."error_types" ("errdesc", "autofix") VALUES ('Attempt to change reknr', False);
INSERT INTO "public"."error_types" ("errdesc", "autofix") VALUES ('Update transactiondate', True);
INSERT INTO "public"."error_types" ("errdesc", "autofix") VALUES ('Attempt to change transactiondate', False);
INSERT INTO "public"."error_types" ("errdesc", "autofix") VALUES ('Update invoiceid', True);
INSERT INTO "public"."error_types" ("errdesc", "autofix") VALUES ('Attempt to change invoiceid', False);
INSERT INTO "public"."error_types" ("errdesc", "autofix") VALUES ('Update lineamountmst', True);
INSERT INTO "public"."error_types" ("errdesc", "autofix") VALUES ('Attempt to change lineamountmst', False);
CREATE TYPE test3_type AS (
myid integer,
myerrdesc text
);
CREATE OR REPLACE FUNCTION test3 () RETURNS SETOF test3_type AS
$body$
DECLARE
rec test3_type;
BEGIN
FOR rec IN (
SELECT id, errdesc
FROM error_types)
LOOP
RETURN NEXT rec;
END LOOP;
RETURN;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
select * from test3() order by myerrdesc desc;
myidmyerrdesc
11Update transactiondate
9Update reknr
15Update lineamountmst
13Update invoiceid
8Original invoice not found
1Missing InvoiceID
2Missing InventTransID
7Invoice with a negative amount
3Illegal oneshot dates (start <> end)
4Illegal dates (start > end)
6Creditnote with a positive amount
12Attempt to change transactiondate
10Attempt to change reknr
16Attempt to change lineamountmst
14Attempt to change invoiceid
5Accountnumber not defined
I hope this is useful to you.
>>> "A. Kretschmer" <andreas.kretschmer@schollglas.com> 2008-01-30 11:42 >>>
am Wed, dem 30.01.2008, um 11:35:51 +0100 mailte Jaroslav Sivy folgendes:
> Hello everyone,
>
> I have following problem: am using pl/sql functions to trigger some
> sql code and i need to pass ORDER_BY column name and ASC/DESC sorting
> order as an input parameters into that function and order the result
> based on these input parameters.
>
> The problem is, that the only way is to create query as "string" and
> then execute it as "string".
Right.
>
> Is there any other way around how to avoid that "string query"?
No.
> If it's not possible, could anyone implement this feature into one of
> future versions? I think that it would be quite handy to have
Unlikely...
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ( http://wwwkeys.de.pgp.net/ )
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: Bart.Degryse@indicator.be
Subject: Re: Sql ORDER BY and ASC/DESC question
In-Reply-To: <47A07C0C.A3DD.0030.0@indicator.be>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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