Thread: functions behaviours

Hi All!

I'm wondering if there is an elegant way to add simple behaviours to stored Postgres functions.

1. First, what I want to get here is a function which orders their results columns in ASC at one time and in DESC next
timeit is called. Something like that: 

CREATE FUNCTION sort_swap(integer) RETURNS SETOF atype AS '

   sort_type ALIAS FOR $1;
   row atype;

   IF sort_type = 'ASC' THEN

    FOR row IN SELECT column1 ASC, column2, column3 FROM table
               ORDER BY column1 ASC, column2, column3
      LOOP
         RETURN NEXT row;
      END LOOP;

   ELSE

     FOR row IN SELECT column1 ASC, column2, column3 FROM table
               ORDER BY column1 DESC, column2, column3
      LOOP
         RETURN NEXT row;
      END LOOP;

   END IF;

    RETURN;
' LANGUAGE plpgsql;

What I want here is a function without the sort_type parameter.

2. Second, is it possible to give a function a parameter which correspond to a table name?

CREATE FUNCTION function_with_behaviours(integer) RETURNS SETOF atype AS '

   table_name ALIAS FOR $1;
   row atype;

      SELECT INTO row *  FROM table_name;

    RETURN row;
' LANGUAGE plpgsql;

In that case, how to dynamically adapt  atype  to the be table_name%ROWTYPE ?


Thanks in advance
Youn

am  04.04.2006, um 15:02:19 +0200 mailte ycrux@club-internet.fr folgendes:
> Hi All!
>
> I'm wondering if there is an elegant way to add simple behaviours to stored Postgres functions.
>
> 1. First, what I want to get here is a function which orders their
> results columns in ASC at one time and in DESC next time it is called.
> Something like that:
>
> CREATE FUNCTION sort_swap(integer) RETURNS SETOF atype AS '
>
>    sort_type ALIAS FOR $1;
>    row atype;
>
>     RETURN;
> ' LANGUAGE plpgsql;
>
> What I want here is a function without the sort_type parameter.

Write 2 functions, one for ASC and one for DESC?


> 2. Second, is it possible to give a function a parameter which correspond to a table name?
>
> CREATE FUNCTION function_with_behaviours(integer) RETURNS SETOF atype AS '
>
>    table_name ALIAS FOR $1;
>    row atype;
>
>       SELECT INTO row *  FROM table_name;
>
>     RETURN row;
> ' LANGUAGE plpgsql;
>
> In that case, how to dynamically adapt  atype  to the be table_name%ROWTYPE ?

http://www.postgresql.org/docs/8.1/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN


HTH, Andreas
--
Andreas Kretschmer    (Kontakt: siehe Header)
Heynitz:  035242/47215,      D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
 ===    Schollglas Unternehmensgruppe    ===