functions behaviours

Started by Ycruxabout 20 years ago2 messagesgeneral
Jump to latest
#1Ycrux
ycrux@club-internet.fr

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

#2A. Kretschmer
andreas.kretschmer@schollglas.com
In reply to: Ycrux (#1)
Re: functions behaviours

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 ===