Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-sql by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group