Postgresql execute ошибка синтаксиса

I think your problem is the language you’re using. EXECUTE in the SQL language:

EXECUTE is used to execute a previously prepared statement. Since prepared statements only exist for the duration of a session, the prepared statement must have been created by a PREPARE statement executed earlier in the current session.

isn’t the same as EXECUTE in PL/pgSQL:

Oftentimes you will want to generate dynamic commands inside your PL/pgSQL functions, that is, commands that will involve different tables or different data types each time they are executed. PL/pgSQL’s normal attempts to cache plans for commands (as discussed in Section 39.10.2) will not work in such scenarios. To handle this sort of problem, the EXECUTE statement is provided:

EXECUTE command-string [ INTO [STRICT] target ] [ USING expression [, ... ] ];

You’re using the SQL EXECUTE (which executes a prepared statement) when you want to be using the PL/pgSQL EXECUTE (which executes a string as SQL).

Try this:

CREATE OR REPLACE FUNCTION example() RETURNS void AS $$
BEGIN
    EXECUTE 'INSERT INTO table1 (col1, col2, col3) SELECT col1, col2, col3 from temp_table';
END;
$$ LANGUAGE PLPGSQL;

Or, another example that seems closer to what you seem to be trying to do:

create or replace function example(tname text) returns void as $$
begin
    execute 'insert into ' || tname || ' (name) values(''pancakes'')';
end;
$$ language plpgsql;

That will insert 'pancakes' into the table that you pass in the tname argument to the function.

answered Jul 28, 2011 at 17:15

It is a example tested by me where I use EXECUTE to run a select and put its result in a cursor.

1. Create the table:

create table people (
  nickname varchar(9),
  name varchar(12),
  second_name varchar(12),
  country varchar(30)
  );

2. Create the function:

CREATE OR REPLACE FUNCTION fun_find_people (col_name text, col_value varchar)
RETURNS void AS
$BODY$
DECLARE
    local_cursor_p refcursor;
    row_from_people RECORD;

BEGIN
    open local_cursor_p FOR
        EXECUTE 'select * from people where '|| col_name || ' LIKE ''' || col_value || '%'' ';

    raise notice 'col_name: %',col_name;
    raise notice 'col_value: %',col_value;

    LOOP
        FETCH local_cursor_p INTO row_from_people; EXIT WHEN NOT FOUND;

        raise notice 'row_from_people.nickname: %',  row_from_people.nickname ;
        raise notice 'row_from_people.name: %', row_from_people.name ;
        raise notice 'row_from_people.country: %', row_from_people.country;
    END LOOP;
END;
$BODY$ LANGUAGE 'plpgsql'

3. Run the function

select fun_find_people('name', 'Cristian');
select fun_find_people('country', 'Chile');


answered Feb 18, 2017 at 22:54

Alternatively, you can run it inside an anonymous code-block using DO.
According to the documentation (emphasis mine):

DO executes an anonymous code block, or in other words a transient anonymous function in a procedural language.

The code block is treated as though it were the body of a function with no parameters, returning void. It is parsed and executed a single time.

This allows you to run constructed SQL that you would normally not be able to run, without forcing you to build a function to call it:

DO $$
BEGIN
    execute 'ALTER DATABASE ' || current_database() || ' SET timezone TO ''UTC''';
    execute 'SET timezone TO ''UTC''';
END;
$$

instead of:

CREATE OR REPLACE FUNCTION fix_database_timezone()
RETURNS void AS
$BODY$
BEGIN
    execute 'ALTER DATABASE ' || current_database() || ' SET timezone TO ''UTC''';
    execute 'SET timezone TO ''UTC''';
END;
$BODY$ LANGUAGE 'plpgsql';

fix_database_timezone();

Executing a Command That Does Not Yield a Result

At times, it can be beneficial to assess an expression or SELECT query without retaining the outcome. This can be particularly useful when invoking a function that yields side-effects but lacks a meaningful result value. In PL/pgSQL, the PERFORM statement is utilized for this purpose.

emphasis mine.

Modify the function change from

select

to

perform

.

I wish to execute the second statement exclusively when cnt is equal to zero.

The text can be restated as one sentence.

UPDATE table1 
SET field1 = ...
WHERE id = ...
  AND NOT EXISTS (SELECT * 
                  FROM table1 
                  WHERE field2 = ...
                     AND field1 != ... 
                     AND id != ...);

If the intention is to achieve partial uniqueness, utilizing it in a trigger suggests an attempt at implementation. In such cases, considering the use of a partial/filtered index is also a viable alternative.

CREATE UNIQUE INDEX uq ON table1(id, field1) WHERE field2 = ....;

Even though @Lukasz’s solution could be effective, I ultimately opted for the implementation recommended by @stickybit in the question’s comments.

Answer:

CREATE OR REPLACE FUNCTION my_function() RETURNS TRIGGER AS $$ 
DECLARE 
    cnt bigint;
BEGIN 
    IF NEW.field1 = 'DECLINED' THEN 
        cnt := ('SELECT count(*) FROM table1 WHERE field2 = NEW.field2 AND field1 != NEW.field1 AND id != NEW.id;')
        IF cnt = 0 THEN
             'UPDATE table1 SET field1 = 'DECLINED' WHERE id = NEW.field2';
         END IF;
    END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Postgres 11 introduced the

EXECUTE FUNCTION

syntax for

CREATE TRIGGER

statements.

Instead of saying

EXECUTE PROCEDURE

in Postgres 10, you need to use it.

With the implementation of procedures in Postgres 11, the previously deprecated syntax can no longer be used to implement a trigger. Procedures are separate from functions and serve a distinct purpose.

To resolve the issue, the solution entails substituting the «ELSE IF» statement with «ELSIF»…

Additionally, I made modifications such as converting the strings ‘years’ into integers (‘1874’ -> 1874) and replacing startyear with the new variable NEW.startyear.

CREATE OR REPLACE FUNCTION title_basics_partitioner() 
RETURNS TRIGGER AS $$ 
BEGIN  
    IF (NEW.startyear is null) THEN
        INSERT INTO startyear_null VALUES (NEW.*);
    ELSIF (NEW.startyear >= 1874 AND NEW.startyear < 1894) THEN
        INSERT INTO startyear_1874_1894 VALUES (NEW.*);
    ELSIF (NEW.startyear >= 1894 AND NEW.startyear < 1914) THEN
        INSERT INTO startyear_1894_1914 VALUES (NEW.*);
    ELSIF (NEW.startyear >= 1914 AND NEW.startyear < 1934) THEN
        INSERT INTO startyear_1914_1934 VALUES (NEW.*);
    ELSIF (NEW.startyear >= 1934 AND NEW.startyear < 1954) THEN
        INSERT INTO startyear_1934_1954 VALUES (NEW.*);
    ELSIF (NEW.startyear >= 1954 AND NEW.startyear < 1974) THEN
        INSERT INTO startyear_1954_1974 VALUES (NEW.*);
    ELSIF (NEW.startyear >= 1974 AND NEW.startyear < 1994) THEN
        INSERT INTO startyear_1974_1994 VALUES (NEW.*);
    ELSIF (NEW.startyear >= 1994 AND NEW.startyear < 2014) THEN
        INSERT INTO startyear_1994_2014 VALUES (NEW.*);
    ELSIF (NEW.startyear >= 2014 AND NEW.startyear < 2115) THEN
        INSERT INTO startyear_2014_2115 VALUES (NEW.*);
    ELSE         
        RAISE EXCEPTION 'Out of range year value. Fix the title_basics_insert_trigger() function!';
    END IF;     
    RETURN NULL; 
END; 
$$
LANGUAGE plpgsql;