Postgres Pro
Downloads
Home   >   mailing lists

Thread: Stored Procedures and Return Type

Stored Procedures and Return Type

From
Clinton Adams
Date:
Hello,

What would be the rtype for a function to return tuples selected over more
than one table?  The closest I can find is setof but this rtype is for all
columns of only one table.

Example:

states
state_id integer
state_name varchar
state_abbreviation char(2)

districts
district_id integer
state_id integer
district_name varchar


CREATE OR REPLACE FUNCTION districts(varchar, varchar) RETURNS ???? AS '
    SELECT state_name, districtname FROM states s, districts d
    WHERE s.state_id = d.state_id AND s.state_name = $1 AND d.district_name = $2;
' LANGUAGE 'sql';

ElseIF in PLPGSQL

From
denis@coralindia.com
Date:
Hi all,

I have created one function and i want to use

IF
    .........do something...
ELSEIF
    .........do something...
ELSE
    .........do something...
END IF;

My ELSEIF condition is TRUE but still it goes into ELSE condition ???

Is this a problem or ???

Thanx

Denis



----- Original Message -----
From: "Clinton Adams" <clinton@vote-smart.org>
To: <pgsql-novice@postgresql.org>
Sent: Thursday, February 28, 2002 6:18 AM
Subject: [NOVICE] Stored Procedures and Return Type


> Hello,
>
> What would be the rtype for a function to return tuples selected over more
> than one table?  The closest I can find is setof but this rtype is for all
> columns of only one table.
>
> Example:
>
> states
> state_id integer
> state_name varchar
> state_abbreviation char(2)
>
> districts
> district_id integer
> state_id integer
> district_name varchar
>
>
> CREATE OR REPLACE FUNCTION districts(varchar, varchar) RETURNS ???? AS '
> SELECT state_name, districtname FROM states s, districts d
> WHERE s.state_id = d.state_id AND s.state_name = $1 AND d.district_name =
$2;
> ' LANGUAGE 'sql';
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly

Re: ElseIF in PLPGSQL

From
"Josh Berkus"
Date:
Denis,

There is no ELSEIF in PL/pgSQL.  You have to use nested IF ... THEN ...
 ELSE statements instead.

-Josh

Re: ElseIF in PLPGSQL

From
Tom Lane
Date:
"Josh Berkus" <josh@agliodbs.com> writes:
> There is no ELSEIF in PL/pgSQL.

There is, but IIRC it is new in 7.2.

            regards, tom lane