functions behaviours

From: ycrux(at)club-internet(dot)fr
To: pgsql-general(at)postgresql(dot)org
Subject: functions behaviours
Date: 2006-04-04 13:02:19
Message-ID: mnet1.1144155739.9536.ycrux@club-internet.fr
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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;

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Courtenay Teska 2006-04-04 13:03:46 Re: giving users access to specific databases
Previous Message Ottavio Campana 2006-04-04 12:59:12 Re: database design questions