PL/pgSQL question

Ycrux <ycrux@club-internet.fr> writes:

# SELECT grantAccess('sara', 'sarapass');
ERROR: set-valued function called in context that cannot accept a set

You need to do "SELECT * FROM grantAccess(...)". This is a plpgsql
implementation restriction that we'll probably try to fix someday,
although there's also a school of thought that says that set-returning
functions in the SELECT targetlist are a bad idea and should be phased
out.

regards, tom lane

Hi All!
First of all, a great Thanks, your suggestions works fine.

I'll hope to enhance a little bit my understanding of SETOF return type.
I have now two problems.

1) I would like to return some columns from one table in PL/pgSQL function. What's in this case the correct return type of the PL/pgSQL function. This is a pseudo-code for my first problem:

--------------------------------------------------------------------
CREATE FUNCTION function_name (sometypes) RETURNS return_type AS $$
FOR some_type IN SELECT column1, column3 FROM tablename WHERE some_conditions
LOOP
RETURN NEXT some_type;
END LOOP;
RETURN;
$$ LANGUAGE 'plpgsql' STABLE;
--------------------------------------------------------------------
What's return_type and some_type in this case?

2) The next problem is almost same as above. But now, I would like to return different columns from different tables.
What's in this case the correct return type of PL/pgSQL function.
This is a pseudo-code for my second problem:

--------------------------------------------------------------------
CREATE FUNCTION function_name (sometypes) RETURNS return_type AS $$
FOR some_type IN SELECT column1_table1, column17_table2 FROM table1, table2 WHERE some_conditions
LOOP
RETURN NEXT some_type;
END LOOP;
RETURN;
$$ LANGUAGE 'plpgsql' STABLE;
--------------------------------------------------------------------

Thanks in advance
Younes

----Message d'origine----

ycrux@club-internet.fr wrote:

Hi All!
First of all, a great Thanks, your suggestions works fine.

I'll hope to enhance a little bit my understanding of SETOF return type.
I have now two problems.

1) I would like to return some columns from one table in PL/pgSQL function. What's in this case the correct return type of the PL/pgSQL function. This is a pseudo-code for my first problem:

--------------------------------------------------------------------
CREATE FUNCTION function_name (sometypes) RETURNS return_type AS $$
FOR some_type IN SELECT column1, column3 FROM tablename WHERE some_conditions
LOOP
RETURN NEXT some_type;
END LOOP;
RETURN;
$$ LANGUAGE 'plpgsql' STABLE;
--------------------------------------------------------------------
What's return_type and some_type in this case?

Depends on what column1,column3 are. See the manuals for CREATE TYPE.
If column1 was int4 and column3 was a date you'd do something like:
CREATE TYPE return_type AS (
a int4,
b date
);

some_type is a variable not a type definition, although you'd probably
define it to be of type "return_type".

Oh, and it should be ... RETURNS SETOF return_type

2) The next problem is almost same as above. But now, I would like to return different columns from different tables.
What's in this case the correct return type of PL/pgSQL function.
This is a pseudo-code for my second problem:

--------------------------------------------------------------------
CREATE FUNCTION function_name (sometypes) RETURNS return_type AS $$
FOR some_type IN SELECT column1_table1, column17_table2 FROM table1, table2 WHERE some_conditions
LOOP
RETURN NEXT some_type;
END LOOP;
RETURN;
$$ LANGUAGE 'plpgsql' STABLE;

Same difference, but you would change your type definition.

--
Richard Huxton
Archonet Ltd