CODEWITHSUNDEEP
postgresql
Natysiu16
Natysiu16

Reputation: 366

unexpected end of function definition on PostgreSQL function

What is wrong with this function? EDITED>>

CREATE OR REPLACE FUNCTION on_ai_myTable() RETURNS TRIGGER AS $$

BEGIN
SELECT fn_name, count(*) + 1 FROM table_ebscb_spa_log02 WHERE time_type = 'Start' GROUP BY fn_name
RETURN NEW.fn_name;
END
$$
LANGUAGE plpgsql;

Ok I added the ";" but now, when I try to create it, it send me the message ERROR: syntax error at or near "RETURN" LINE 5: RETURN fn_name;

How can I solve it????

Thanks Advanced.

Upvotes: 6

Views: 13999

Answers (2)

korry
korry

Reputation: 866

You need a semicolon at the end of the SELECT statement, and another semicolon following the END:

CREATE OR REPLACE FUNCTION on_ai_myTable() RETURNS TRIGGER AS $$
  BEGIN
    SELECT fn_name, count(*) + 1 FROM table_ebscb_spa_log02 WHERE time_type = 'Start' GROUP BY fn_name;
    RETURN NEW.fn_name;
  END;
$$
LANGUAGE plpgsql;

See PL/pgSQl block layout And Executing a query with a single-row result

Also note that you will need to change the SELECT statement - the way you have it written now, the PL/pgSQL compiler will complain that it has no place to put the results (you've requested two values, fn_nam and count(*) + 1), but you are missing an INTO clause.

Upvotes: 2

user_0
user_0

Reputation: 3363

You just need to add semicolons:

CREATE OR REPLACE FUNCTION on_ai_myTable() RETURNS TRIGGER AS $$

BEGIN
SELECT fn_name, count(*) + 1 FROM table_ebscb_spa_log02 WHERE time_type = 'Start' GROUP BY fn_name;
RETURN NEW.fn_name;
END
$$
LANGUAGE plpgsql;

Upvotes: 7

Related Questions