Обсуждение: Function Syntax Help

Поиск
Список
Период
Сортировка

Function Syntax Help

От
Dennis Ryan
Дата:
I having trouble with correct syntax to get this trigger function to compile.  I have tried every combination of removing the ‘;’ characters but the function will not compile.  Can someone tell me what I am doing wrong, I am stumped. I will be adding addition when clauses the case statement once I get this simplified version to compile.

I am using 9.3.4, both postgres and psql.


CREATE OR REPLACE FUNCTION sn_dm_b.pm_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
    CASE
        WHEN NEW.period =  201001
                THEN INSERT INTO sn_dm_b.pm201001 VALUES (NEW.*);
    END;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;


ERROR:  syntax error at or near ";"
LINE 7:     END;

Re: Function Syntax Help

От
Raymond O'Donnell
Дата:
On 25/06/2014 23:19, Dennis Ryan wrote:
> I having trouble with correct syntax to get this trigger function to
> compile.  I have tried every combination of removing the ‘;’ characters
> but the function will not compile.  Can someone tell me what I am doing
> wrong, I am stumped. I will be adding addition when clauses the case
> statement once I get this simplified version to compile.
>
> I am using 9.3.4, both postgres and psql.
>
>
> CREATE OR REPLACE FUNCTION sn_dm_b.pm_insert_trigger()
> RETURNS TRIGGER AS $$
> BEGIN
>     CASE
>         WHEN NEW.period =  201001
>                 THEN INSERT INTO sn_dm_b.pm201001 VALUES (NEW.*);

The problem is the semi-colon after (NEW.*). There isn't one inside a
CASE construct.

Ray.


>     END;
>     RETURN NULL;
> END;
> $$ LANGUAGE plpgsql;
>
>
> ERROR:  syntax error at or near ";"
> LINE 7:     END;


--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

Re: Function Syntax Help

От
Pavel Stehule
Дата:
Hello

You are using PLpgSQL CASE statement

this start by CASE keyword and finishing by END CASE keywords

CREATE OR REPLACE FUNCTION sn_dm_b.pm_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
    CASE
        WHEN NEW.period =  201001
                THEN INSERT INTO sn_dm_b.pm201001 VALUES (NEW.*);
    END;
    RETURN NULL;
END CASE; -- <<<<<<<<<<<<<
$$ LANGUAGE plpgsql;




2014-06-26 0:19 GMT+02:00 Dennis Ryan <dennis@kabonkulator.com>:
I having trouble with correct syntax to get this trigger function to compile.  I have tried every combination of removing the ‘;’ characters but the function will not compile.  Can someone tell me what I am doing wrong, I am stumped. I will be adding addition when clauses the case statement once I get this simplified version to compile.

I am using 9.3.4, both postgres and psql.


CREATE OR REPLACE FUNCTION sn_dm_b.pm_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
    CASE
        WHEN NEW.period =  201001
                THEN INSERT INTO sn_dm_b.pm201001 VALUES (NEW.*);
    END;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;


ERROR:  syntax error at or near ";"
LINE 7:     END;

Re: Function Syntax Help

От
Shaun Thomas
Дата:
On 06/25/2014 05:19 PM, Dennis Ryan wrote:

>  CASE
>      WHEN NEW.period =  201001
>              THEN INSERT INTO sn_dm_b.pm201001 VALUES (NEW.*);
>  END;

You can't just have a bare CASE statement in plpgsql. Try this:

CREATE OR REPLACE FUNCTION sn_dm_b.pm_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
     IF NEW.period = 201001 THEN
         INSERT INTO sn_dm_b.pm201001 VALUES (NEW.*);
     END IF;
     RETURN NULL;
END;
$$ LANGUAGE plpgsql;

--
Shaun Thomas
OptionsHouse, LLC | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
sthomas@optionshouse.com

______________________________________________

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email

Re: Function Syntax Help

От
Raymond O'Donnell
Дата:
On 26/06/2014 17:26, Raymond O'Donnell wrote:
> On 25/06/2014 23:19, Dennis Ryan wrote:
>> I having trouble with correct syntax to get this trigger function to
>> compile.  I have tried every combination of removing the ‘;’ characters
>> but the function will not compile.  Can someone tell me what I am doing
>> wrong, I am stumped. I will be adding addition when clauses the case
>> statement once I get this simplified version to compile.
>>
>> I am using 9.3.4, both postgres and psql.
>>
>>
>> CREATE OR REPLACE FUNCTION sn_dm_b.pm_insert_trigger()
>> RETURNS TRIGGER AS $$
>> BEGIN
>>     CASE
>>         WHEN NEW.period =  201001
>>                 THEN INSERT INTO sn_dm_b.pm201001 VALUES (NEW.*);
>
> The problem is the semi-colon after (NEW.*). There isn't one inside a
> CASE construct.

Whoops - Pavel is of course correct - this is a pl/pgsql CASE, not an
SQL one. Sorry - my mistake.

Ray.


--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

Re: Function Syntax Help

От
Pavel Stehule
Дата:



2014-06-26 18:28 GMT+02:00 Shaun Thomas <sthomas@optionshouse.com>:
On 06/25/2014 05:19 PM, Dennis Ryan wrote:

 CASE
     WHEN NEW.period =  201001
             THEN INSERT INTO sn_dm_b.pm201001 VALUES (NEW.*);
 END;

You can't just have a bare CASE statement in plpgsql. Try this:


CREATE OR REPLACE FUNCTION sn_dm_b.pm_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.period = 201001 THEN

        INSERT INTO sn_dm_b.pm201001 VALUES (NEW.*);
    END IF;

    RETURN NULL;
END;
$$ LANGUAGE plpgsql;


Regards

Pavel
 

--
Shaun Thomas
OptionsHouse, LLC | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
sthomas@optionshouse.com

______________________________________________

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: Function Syntax Help

От
Pavel Stehule
Дата:



2014-06-26 18:26 GMT+02:00 Raymond O'Donnell <rod@iol.ie>:
On 25/06/2014 23:19, Dennis Ryan wrote:
> I having trouble with correct syntax to get this trigger function to
> compile.  I have tried every combination of removing the ‘;’ characters
> but the function will not compile.  Can someone tell me what I am doing
> wrong, I am stumped. I will be adding addition when clauses the case
> statement once I get this simplified version to compile.
>
> I am using 9.3.4, both postgres and psql.
>
>
> CREATE OR REPLACE FUNCTION sn_dm_b.pm_insert_trigger()
> RETURNS TRIGGER AS $$
> BEGIN
>     CASE
>         WHEN NEW.period =  201001
>                 THEN INSERT INTO sn_dm_b.pm201001 VALUES (NEW.*);

The problem is the semi-colon after (NEW.*). There isn't one inside a
CASE construct.

no, using INSERT inside SQL CASE is not possible (with or without semicolon), but inside PL/pgSQL it is ok. But PL/pgSQL statement uses END CASE like others END .. END IF, END LOOP,

Regards

Pavel
 

Ray.


>     END;
>     RETURN NULL;
> END;
> $$ LANGUAGE plpgsql;
>
>
> ERROR:  syntax error at or near ";"
> LINE 7:     END;


--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general