Обсуждение: sql query with join and parameter in postgresql function

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

On Jan 16, 2004, at 8:30 PM, Philippe Lang wrote:

> Hello,
>
> I'd like to write a function similar to that:
>
> ------------------------------------------------
> CREATE FUNCTION public.report_join_parameter(char(6))
>   RETURNS ??? AS

<snip />

> Do I ***HAVE TO*** define a composite type for the columns my function
> is supposed to return?

As far as I know. It's not that hard.

CREATE TYPE report_join_type (tableAfield1 <type>,tableAfield2 <type>,tableBfield1 <type>,tableBfield2
<type>,tableCfield1<type>,tableCfield2 <type>
 
);

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

But you can just define the view and select it with a WHERE clause with 
your parameter, just like a normal table. Would that help?

Michael Glaesemann
grzm myrealbox com

> > I thought a view would be more appropriate, but
> > if I'm not wrong, a view does not accept any parameter
> > as input...
>
> But you can just define the view and select it with a WHERE clause
with
> your parameter, just like a normal table. Would that help?

Thanks! That's perfect. I join in a view, and limit in a function, with
a WHERE clause. And no need for any redundant composite type, although I
agree this is not that complicated to use...

Philippe Lang