1

I have a PL/PgSQL function like this:

CREATE FUNCTION get_value(firstval integer) RETURNS SETOF mytable AS
$func$
DECLARE
    current mytable;
BEGIN
    SELECT fv FROM mytable WHERE fv = fistval INTO current;
    IF current IS NULL THEN
        INSERT INTO mytable(fv) VALUES (firstval);
    END IF;
    RETURN current.fv;
END
$func$ LANGUAGE plpgsql;

I need to return a specific row (fv) of the current variable, but this code is not working (it does not return anything), so how I have to do this?

CrisMystik
  • 11
  • 1
  • It looks like you are declaring variable "mytable" within the function and then selecting from it before it has any value. So its always NULL. – Soren Dec 06 '20 at 12:56

1 Answers1

0

There are lot of issues:

  1. current is declared like record. But you try to assign integer value to this composite variable SELECT INTO.

  2. The return from table functions should be realised by RETURN TABLE statement. There are not any table with name mytable. In Postgres concept - RETURNS SETOF mytable means - returns rows of type like table "mytable".

  3. The test IS NULL is not safe in this case. You want to check FOUND variable.

  4. Maybe you don't want to return table - then there is badly used SETOF clause.

    CREATE OR REPLACE FUNCTION get_value(firstval int)
RETURNS mytable AS $$
DECLARE
  _r mytable;
BEGIN
  SELECT * FROM mytable WHERE fv = firstval INTO _r;
  IF NOT FOUND THEN
    INSERT INTO mytable(fv) VALUES(firstval);
    SELECT * FROM mytable WHERE fv = firstval INTO _r;
  END IF;
  RETURN _r;
END;
$$ LANGUAGE plpgsql;

Attention! - this code maybe does what you want, but it is not safe against race conditions. Better to use Postgres statement INSERT ON CONFLICT DO.

Please, try to read documentation first - it is designed differently than you are expecting.

Pavel Stehule
  • 42,331
  • 5
  • 91
  • 94
  • Thanks, reading the documentation better I realized that my code was a mess, but I still didn't find the answer to my question: I would like to be able to access the value of a `_r` column without having to make a new query, mainly for some checks present in the original code and that I didn't write here. – CrisMystik Dec 06 '20 at 13:10
  • I only noticed now that I had written SELECT fv instead of SELECT *, so I assume that's why I couldn't access a specific column and didn't get anything instead of getting an error - I hadn't selected it. Thanks again – CrisMystik Dec 06 '20 at 13:23
  • @CrisMystik if you use `SELECT * INTO _r`, then the value of column `fv` will be available with `_r.fv`. I have not a idea what your function should to return - if you want exactly one value or if you want record (composite value). – Pavel Stehule Dec 06 '20 at 13:30
  • Now I want to return the record - I have to use `SETOF` considerimg that fv is `UNIQUE`? – CrisMystik Dec 06 '20 at 14:09
  • @CrisMystik - no - `SETOF` is used when you want to return 0..N rows. When you want exactly one row, then you cannot to use this clause. My example returns composite value. – Pavel Stehule Dec 06 '20 at 14:48