Parametrised view or generic return value in function

Question

What I am looking for is what I would call a "parametrised view". When looking around on the internet, I learned that that is not supported by PostgreSQL, and that I should use a function instead.

So I create a function:

CREATE OR REPLACE FUNCTION public.student_get_byschool(
    _schoolid integer
) RETURNS SETOF student AS
$body$
    SELECT * FROM student WHERE schoolid=_schoolid;
$body$
LANGUAGE 'sql';

However, if I want to select a little more of that student by joining other records, my RETURNS clause isn't correct anymore. I could fix that in two ways:

Use RETURNS SETOF RECORD, but that seems to have some issues in my C#.NET application, as it doesn't give me any column definitions.
Create a new type that holds all the data that I want to return, but that introduces redundancy which is what I very much want to avoid in my database design.

My question now is, are these the only options, or is more possible. Note that I just started PostgreSQL and C#.NET.

Use returns table (col1 integer, ...)

Clodoaldo Neto
– Clodoaldo Neto

2013-03-08 14:16:31 +00:00
Commented Mar 8, 2013 at 14:16 — Clodoaldo Neto
– Clodoaldo Neto, Commented Mar 8, 2013 at 14:16

Craig Ringer · Accepted Answer · 2013-03-08 14:08:55Z

1

As per the documentation for CREATE FUNCTION you can use OUT parameters or RETURNS TABLE for this. RETURNS TABLE tends to be more readable.

This Postgres Online article may prove to be interesting, though I haven't read it in detail to check it and make sure it's up to date and correct.

answered Mar 8, 2013 at 14:08

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Bart Friederichs Over a year ago

Yes, I also found RETURNS TABLE, and even though it is all in one definition, there is still some redundancy. I am leaning towards views with WHERE clauses now.

Craig Ringer Over a year ago

@BartFriederichs If the number of variants is small, that can be a good choice - and views sometimes inline better, too. If you use functions, use plain SQL functions and do not mark them STRICT if you want them to be able to inline. Some redundancy is unfortunately necessary because Pg needs to know the output type of the query in the function before it can actually check the function body; it's one of the costs of being able to support a variety of pluggable procedural languages.

Bart Friederichs Over a year ago

thanks for the info, I'll keep it in mind. I was also checking of my ideas were valid obviously.

Collectives™ on Stack Overflow

Parametrised view or generic return value in function

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related