| 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: | Whole Thread | Raw Message | 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
| 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 |