Re: Sql ORDER BY and ASC/DESC question - Mailing list 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;

 

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
       choose an index scan if your joining column's datatypes do not
       match