postgresql function creation gives syntax error

Question

I am writing a postgresql function but I cannot seem to find where the error is. Postgresql is at version 9.4.
The following is the function:

CREATE FUNCTION custom_function1()
RETURNS trigger
LANGUAGE plpgsql
AS
$$
    DECLARE base_val integer;
    BEGIN
        base_val := (EXTRACT(YEAR FROM now())::integer * 10000000000);

        IF (currval('custom_sequence') < base_val) THEN
            setval('custom_sequence', base_val);
        END IF;

        NEW.id := custom_function2();
        RETURN NEW;
    END;
$$;

My custom_sequence is in the format YYYY0000000000 (ex. 20150000000000).

So what this basically does (should do) is it checks if the base_val (minimum value for current year) is greater then the currval (current custom_sequence value) and updates the custom_sequence value. Then it returns a new value for that sequence generated with the function custom_function2 (which formats it slighly).

When I try to execute this it gives me:
syntax error at or near "setval"
I am pretty new to both postgresql and writing functions so I am probably not seeying an obvious error. If someone could help me it would be highly appreciated, thank you.

Community · Accepted Answer · 2020-06-20 09:12:55Z

3

The error is related to what's explained in the doc here:

40.5.2 Executing a Command With No Result
[...]

Sometimes it is useful to evaluate an expression or SELECT query but discard the result, for example when calling a function that has side-effects but no useful result value. To do this in PL/pgSQL, use the PERFORM statement:

PERFORM query;

You should write:

PERFORM setval('custom_sequence', base_val);

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Jul 28, 2015 at 15:56

Daniel Vérité

62.3k16 gold badges134 silver badges160 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Francesco D.M. Over a year ago

Thank you very much! And thank you for the explanation link

Collectives™ on Stack Overflow

postgresql function creation gives syntax error

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related