Skip site navigation (1) Skip section navigation (2)

Re: Sql ORDER BY and ASC/DESC question

From: "Bart Degryse" <Bart(dot)Degryse(at)indicator(dot)be>
To: <pgsql-sql(at)postgresql(dot)org>
Subject: Re: Sql ORDER BY and ASC/DESC question
Date: 2008-01-30 12:30:52
Message-ID: 47A07C0C.A3DD.0030.0@indicator.be (view raw or flat)
Thread:
Lists: pgsql-sql
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(dot)kretschmer(at)schollglas(dot)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

In response to

pgsql-sql by date

Next:From: Gregory StarkDate: 2008-01-30 13:32:45
Subject: Re: Sql ORDER BY and ASC/DESC question
Previous:From: A. KretschmerDate: 2008-01-30 10:42:26
Subject: Re: Sql ORDER BY and ASC/DESC question

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group