Function shows error "relation my_table does not exist"

Question

I created a function to generate invoice numbers but when I do:

select get_generated_kodesj()

it shows an error:

relation "transpending_h" does not exist
LINE 19: ...END END END AS "KODETRANSNEW" FROM transpendi...

Here my function declaration:

CREATE FUNCTION get_generated_kodesj()
RETURNS CHAR AS $$
DECLARE kodeSJ CHAR;
BEGIN      
        SELECT
        CASE WHEN MAX(RIGHT("KODETRANS",4)) IS NULL THEN
        CONCAT('SJ-',EXTRACT(YEAR FROM NOW()),LPAD(CAST(EXTRACT(MONTH from NOW()) AS CHAR), 2 ,'0'),'0001')
        ELSE
        CASE WHEN MAX(CAST(RIGHT("KODETRANS",4) AS INTEGER))+1<10 THEN
        CONCAT('SJ-',EXTRACT(YEAR FROM NOW()),LPAD(CAST(EXTRACT(MONTH from NOW()) AS CHAR), 2 ,'0'),'000',
        MAX(CAST(RIGHT("KODETRANS",4) AS INTEGER))+1)
        ELSE
        CASE WHEN MAX(CAST(RIGHT("KODETRANS",4) AS INTEGER))+1<100 AND MAX(CAST(RIGHT("KODETRANS",4) AS INTEGER))+1>=10 THEN
        CONCAT('SJ-',EXTRACT(YEAR FROM NOW()),LPAD(CAST(EXTRACT(MONTH from NOW()) AS CHAR), 2 ,'0'),'00',
        MAX(CAST(RIGHT("KODETRANS",4) AS INTEGER))+1)
        ELSE 
        CASE WHEN MAX(CAST(RIGHT("KODETRANS",4) AS INTEGER))+1<1000 AND MAX(CAST(RIGHT("KODETRANS",4) AS INTEGER))+1>=100 THEN
        CONCAT('SJ-',EXTRACT(YEAR FROM NOW()),LPAD(CAST(EXTRACT(MONTH from NOW()) AS CHAR), 2 ,'0'),'0',
        MAX(CAST(RIGHT("KODETRANS",4) AS INTEGER))+1)
        ELSE 
        CONCAT('SJ-',EXTRACT(YEAR FROM NOW()),LPAD(CAST(EXTRACT(MONTH from NOW()) AS CHAR), 2 ,'0'),
        MAX(CAST(RIGHT("KODETRANS",4) AS INTEGER))+1)
        END END END END AS "KODETRANSNEW" INTO kodeSJ
        FROM transpending_h
        WHERE SUBSTRING("KODETRANS" FROM 5 FOR 4)=CAST(EXTRACT(YEAR from NOW()) AS CHAR)
        AND SUBSTRING("KODETRANS" FROM 9 FOR 2)=CAST(EXTRACT(MONTH from NOW()) AS CHAR);
    RETURN kodeSJ;
END;
$$  LANGUAGE plpgsql
    SECURITY DEFINER
    -- Set a secure search_path: trusted schema(s), then 'pg_temp'.
    SET search_path = admin, pg_temp;

What might be the problem here?

I assume you're sure the relation `transpending_h` does exist in the current schema. Right? — Jim Jones, Apr 19 '18 at 06:53
but i already created this table, and before i declared this function, i run this query (select something), it works. — Agoeng Liu, Apr 19 '18 at 06:54
I see. Well, syntactically your function is just fine. Double check if you're not executing query and function in different schemata / different databases. Good luck! — Jim Jones, Apr 19 '18 at 06:58
security definer means you want to run this `fn()` with some other user, not the fn() creator - right? does that other user has the schema where `transpending_h` locates in his `search_path`?.. — Vao Tsun, Apr 19 '18 at 07:02
You are using case-sensitive column names (`"KODETRANS"` is a different name than `KODETRANS`) maybe your table was created all uppercase as well? So you need to use `from "TRANSPENDING_H"` — , Apr 19 '18 at 07:02
for all fields i did with uppercase but tables name i did lowercase — Agoeng Liu, Apr 19 '18 at 07:14

Erwin Brandstetter · Answer 1 · 2018-04-21T14:48:06.750

You commented:

but i already created this table, and before i declared this function, i run this query (select something), it works.

You created the table, but obviously not in the schema admin, which is the only one in the custom search_path of your function (besides the temp schema and the implicit pg_catalog). Check:

SELECT * FROM pg_tables WHERE tablename = 'transpending_h';

Schema-qualify the table in the function body and the error should go away. Like: myschema.transpending_h.

You operate with the data type char, which is a misunderstanding:
- Compare varchar with char
Use text instead.
Don't use double-quoted identifiers at all if you can avoid it. Just introduces overhead and confusion.
- Are PostgreSQL column names case-sensitive?
You do not need plpgsql for this simple SELECT. I suggest a plain SQL function.

On a closer look, almost everything about your function can be improved. Basically, you are incrementing per-month serial numbers. Could be simplified to something like:

CREATE FUNCTION get_generated_kodesj()
  RETURNS text AS
$func$
   SELECT to_char(now(), '"SJ-"YYYYMM')
       || COALESCE(to_char(MAX(RIGHT("KODETRANS",4))::int + 1, 'FM0000'), '0001')
   FROM   transpending_h
   WHERE  "KODETRANS" LIKE to_char(now(), '"SJ-"YYYYMM"%"')
                        -- assuming your codes start with "SJ-"; else adapt
$func$  LANGUAGE sql
        SECURITY DEFINER
        SET search_path = admin, pg_temp;

Remove blank-padding from to_char() output

But the whole approach still suffers from race conditions in a multi-user environment. Use a global serial or IDENTITY column (in combination with a date or timestamp) instead of trying to create your own per-month serial. See:

If you need per-month serial numbers, consider a different approach to avoid race conditions in multi-user environments:

How to perform conditional insert based on row count?

Function shows error "relation my_table does not exist"

1 Answers1

Asides

Linked