sql query with join and parameter in postgresql function

From: "Philippe Lang" <philippe(dot)lang(at)attiksystem(dot)ch>
To: <pgsql-sql(at)postgresql(dot)org>
Subject: sql query with join and parameter in postgresql function
Date: 2004-01-16 11:30:56
Message-ID: 6C0CF58A187DA5479245E0830AF84F4208018E@poweredge.attiksystem.ch
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Hello,

I'd like to write a function similar to that:

------------------------------------------------
CREATE FUNCTION public.report_join_parameter(char(6))
RETURNS ??? AS
'
SELECT

tableA.field1,
tableA.field2,
tableB.field1,
tableB.field2,
tableC.field1,
tableC.field2,

FROM tableA

INNER JOIN tableB
ON tableA.pk = tableB.fk

INNER JOIN tableC
ON tableB.pk = tableC.fk

WHERE tableC.field3= 0
AND tableB.field1= $1
'
LANGUAGE 'sql' VOLATILE;
------------------------------------------------

Do I ***HAVE TO*** define a composite type for the columns my function
is supposed to return? Isn't there a simpler method, that could allow me
to avoid this extra work?

I thought a view would be more appropriate, but if I'm not wrong, a view
does not accept any parameter as input...

Thanks!

Philippe

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Christoph Haller 2004-01-16 11:34:44 Re: problem with function trigger
Previous Message Christoph Haller 2004-01-16 10:01:08 Re: Email function using c instead of tclu