Postgresql – relation “table” does not exist

functionspermissionsplpgsqlpostgresqlschema

i created the following schema , inside that schema i created a table and inserted a some values.

After that i created a function that get a value in the table based on an ID.

Here is all the declarations :

CREATE SCHEMA pckg_50d_facture;
CREATE TABLE pckg_50d_facture.valeur(val_id text PRIMARY KEY, val text);

CREATE OR REPLACE FUNCTION pckg_50d_facture.const(id text) RETURNS text AS $body$
DECLARE
result text;
BEGIN
  --EXECUTE 'SELECT val FROM pckg_50d_facture.valeur WHERE val_id = id' INTO result USING checked_user, checked_date;

    SELECT val INTO result FROM pckg_50d_facture.valeur WHERE val_id = id;

 return result;
END;
$body$
LANGUAGE PLPGSQL
;

But when i call the function const i get the following error :

ERROR: relation "valeur" does not exist

I tried to Grant some rights as follow : 

GRANT USAGE ON SCHEMA pckg_50d_facture TO PUBLIC;
GRANT SELECT ON ALL TABLES IN SCHEMA pckg_50D_facture TO PUBLIC;

but still not working.

Best Answer

The error message is unrelated to privileges. The table valeur simply cannot be found. There must be some kind of confusion. My educated guess:

You call the function without schema qualification and accidentally execute a different function of the same name, because pckg_50d_facture is not in the search_path (or is there but in a later position). See:

Or you connected to the wrong database / database cluster by mistake.

Related Question