0

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.

1
  • Please tag your PostgreSQL version.
    – McNets
    Sep 7, 2021 at 9:26

1 Answer 1

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.