Using variables in PostgreSQL function

function, navicat, postgresql, variables

Solution

From the console you need a `select` command

select add_translation('one', 'un');

or

select * from add_translation('one', 'un');

Your function can be plain SQL

create or replace function add_translation (
    english varchar(160), français varchar(160)
) returns integer as $body$
    insert into translations ("english", "français")
    values (english, français)
    returning id as translation_id;
$body$ language sql;

In plpgsql a `setof` some type must be "returned from" the query

create or replace function add_translation (
    english varchar(160), français varchar(160)
) returns setof integer as $body$
begin
    return query
    insert into translations ("english", "français")
    values (english, français)
    returning id as translation_id;
end;
$body$ language plpgsql;

Or to return a single value do the insert inside a CTE

create or replace function add_translation (
    english varchar(160), français varchar(160)
) returns integer as $body$
declare
  translation_id integer;
begin
    with i as (
        insert into translations ("english", "français")
        values (english, français)
        returning id
    )
    select id into translation_id from i;
    return translation_id;
end;
$body$ language plpgsql;

Problem

I get this error when I try to run a custom PostgreSQL function: ``` ERROR: query has no destination for result data ``` PostgreSQL functions are very new for me. I am working with Navicat for PostgreSQL 11.0.17. I have a table named translations with three columns: id, english, français. Here is how I create my function in the Console window: ``` test=# CREATE FUNCTION add_translation(english varchar(160), français varchar(160)) RETURNS integer AS $BODY$ DECLARE translation_id integer; BEGIN INSERT INTO translations ("english", "français") VALUES (english, français) RETURNING id AS translation_id; RETURN translation_id; END; $BODY$ LANGUAGE plpgsql; Query OK, 0 rows affected (0.02 sec) ``` When I call this from the Console window, I get a not-very-useful error message. ``` test=# add_translation('one', 'un'); ERROR: syntax error at or near "add_translation" LINE 1: add_translation('one', 'un') ^ ``` When I call it from the Design Function window, I get the error quoted at the top. I specifically want to isolate translation_id, because in the final version of this function I want to insert the latest id from the translation table into a new record in a different table. I have also tried with: ``` DECLARE translation_id integer; BEGIN INSERT INTO translations ("english", "français") VALUES (english, français); SELECT LASTVAL() INTO translation_id; RETURN translation_id; END; ``` When I run this from the Design Function panel, it behaves correctly, but when I call it from the console I get the same error as before. If you can recommend any good tutorials and examples for understanding how to use variables correctly in postgres functions, I would be most grateful.

Original source