Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 487852E00AA for ; Wed, 30 Jan 2008 08:36:50 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 66554-10 for ; Wed, 30 Jan 2008 08:36:37 -0400 (AST) Received: from interscan.indicator.com (mail.indicator.be [193.121.199.3]) by postgresql.org (Postfix) with ESMTP id BB14A2E0063 for ; Wed, 30 Jan 2008 08:36:43 -0400 (AST) Received: from webaccess.indicator.be ([10.100.1.7]) by interscan.indicator.com with InterScan Message Security Suite; Wed, 30 Jan 2008 13:26:49 +0100 Received: from DOMEXT-MTA by webaccess.indicator.bewith Novell_GroupWise; Wed, 30 Jan 2008 13:30:59 +0100 Message-Id: <47A07C0C.A3DD.0030.0@indicator.be> X-Mailer: Novell GroupWise Internet Agent 7.0.1 Date: Wed, 30 Jan 2008 13:30:52 +0100 From: "Bart Degryse" To: Subject: Re: Sql ORDER BY and ASC/DESC question References: <20080130104226.GF26490@a-kretschmer.de> In-Reply-To: <20080130104226.GF26490@a-kretschmer.de> Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="=__Part0E28E4EC.0__=" X-imss-version: 2.049 X-imss-result: Passed X-imss-approveListMatch: *@indicator.be X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200801/228 X-Sequence-Number: 30081 --=__Part0E28E4EC.0__= Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: quoted-printable 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=20 having to use execute. =20 CREATE TABLE "public"."error_types" ( "id" SERIAL,=20 "errdesc" TEXT NOT NULL,=20 "autofix" BOOLEAN DEFAULT false NOT NULL,=20 CONSTRAINT "error_types_errdesc_key" UNIQUE("errdesc"),=20 CONSTRAINT "error_types_pkey" PRIMARY KEY("id") ) WITH (fillfactor =3D 100, OIDS =3D FALSE); =20 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 ('Accountn= umber not defined', False); INSERT INTO "public"."error_types" ("errdesc", "autofix") VALUES ('Creditno= te 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); =20 CREATE TYPE test3_type AS ( myid integer, myerrdesc text ); =20 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; =20 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 =20 I hope this is useful to you. >>> "A. Kretschmer" 2008-01-30 11:42 = >>> am Wed, dem 30.01.2008, um 11:35:51 +0100 mailte Jaroslav Sivy folgendes: > Hello everyone, >=20 > 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. >=20 > The problem is, that the only way is to create query as "string" and > then execute it as "string". Right. >=20 > 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 --=20 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 --=__Part0E28E4EC.0__= Content-Type: text/html; charset=US-ASCII Content-Transfer-Encoding: quoted-printable Content-Description: HTML
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("err= desc"),
  CONSTRAINT "error_types_pkey" PRIMARY KEY("id")
) = WITH (fillfactor =3D 100, OIDS =3D 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"."err= or_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;
= =
myid myerrdesc
11 Update transactiondate
9 Update reknr
15 Update lineamountmst
13 Update invoiceid
8 Original invoice not found
1 Missing InvoiceID
2 Missing InventTransID
7 Invoice with a negative amount
3 Illegal oneshot dates (start <> = end)
4 Illegal dates (start > end)
6 Creditnote with a positive amount
12 Attempt to change transactiondate
10 Attempt to change reknr
16 Attempt to change lineamountmst
14 Attempt to change invoiceid
5 Accountnumber 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

------------------= ---------(end of broadcast)---------------------------
TIP 9: In = versions below 8.0, the planner will ignore your desire to
  &= nbsp;    choose an index scan if your joining column's = datatypes do not
       match
--=__Part0E28E4EC.0__=--