Another missing FROM-clause entry for table

Question

I'm new to postgresql and I have this problem with trigger, that should just take value from the sequence. If I understood correctly, (tried few examples) in case like this you don't need clause FROM (like in ORACLE you would add 'from dual').

Trigger looks something like that:

CREATE OR REPLACE FUNCTION public.trigger_fct_name_id_autoinc()
RETURNS trigger AS
$$
BEGIN
    SELECT "NAME_ID_SEQ".NEXTVAL INTO NEW."ID" ;
RETURN NEW;
END
$$
LANGUAGE 'plpgsql';

Of course, I've done something wrong. Error I get is:

ERROR: missing FROM-clause entry for table "NAME_ID_SEQ" Where: PL/pgSQL function trigger_fct_name_id_autoinc() line 3 at SQL statement

Tried to find some answers in the web, but either there was none, or I don't understand postgresql well enough to understand what people write. In most topics the answer involves aliases, but I don't think that's the case here.

Should I somehow add FROM clause, or is it just a sign of a different issue?

You are using Oracle syntax to access a sequence. This is not how it works in Postgres. Please read the manual: postgresql.org/docs/current/static/… and postgresql.org/docs/current/static/functions-sequence.html — a_horse_with_no_name
Plus you don't need the trigger at all. You can use a sequence as a default value for a column, or even easier use a serial. Again this is documented in the manual: postgresql.org/docs/current/static/ddl-default.html — a_horse_with_no_name

Patrick Hofman Patrick Hofman · Accepted Answer · 2014-12-03T11:35:13

You are using Oracle syntax to get the next value from your sequence. Use the Postgres' nextval:

SELECT nextval('NAME_ID_SEQ') INTO NEW."ID" ;

Another missing FROM-clause entry for table

1 Answers