Re: parse error when executing a simple plpgsql function

Louis Foucart wrote:
> I tried with what you said but I have the same error:
> WARNING:  plpgsql: ERROR during compile of get_column near line 20
> ERROR:  parse error at or near ""

Please keep the thread on the list, so that others can both benefit and
potentially help.

When you use "else if" in plpgsql, it is really just nested "if"
statements. Therefore you need one "end if" for each.

See:
http://www.us.postgresql.org/users-lounge/docs/7.3/postgres/plpgsql-control-structures.html

See below for alternate syntax, "elsif":

> Here is the model:
> create table data_columns(id int8 primary key, father_id int8, denom
> varchar(40), abbrev varchar(15));
> create table material_columns(id int8 primary key, father_id int8, denom
> varchar(40), abbrev varchar(15));
> create table activity_columns(id int8 primary key, father_id int8, denom
> varchar(40), abbrev varchar(15));
>
> create type daf as (father_id int8, denom varchar(40), abbrev varchar(15));
>
> create or replace function get_column(char(3), int8) returns setof daf as
> '
> declare
>     database alias for $1;
>     r daf%rowtype;
> begin
>     if database = ''dat'' then
>         for r in select father_id, denom, abbrev from data_columns where
> id = $2 loop
>             return next r;
>         end loop;
>     else if database = ''mat'' then
       ^^^^^^^
make this elsif

>         for r in select father_id, denom, abbrev from material_columns
> where id = $2 loop
>             return next r;
>         end loop;
>     else if database = ''act'' then
       ^^^^^^^
make this elsif

>         for r in select father_id, denom, abbrev from activity_columns
> where id = $2 loop
>             return next r;
>         end loop;
>     end if;
>     return;
> end
> '
> language 'plpgsql';
CREATE FUNCTION
regression=# select * from get_column('dat', 1);
  father_id | denom | abbrev
-----------+-------+--------
(0 rows)


HTH,

Joe