Solution for Postgres SQL-TG_ARGV within query
is Given Below:
I’m trying to do a trigger that will verify if a record exists in the table passed as argument. I’m doing it as a laravel migration but nervertheless the SQL should be perceptible
DB::unprepared('
CREATE OR REPLACE FUNCTION insert_or_update_lojas() RETURNS trigger AS $insert_or_update_lojas$
DECLARE
store TEXT := TG_ARGV[0]::TEXT;
BEGIN
-- Check if product exists
IF
EXISTS (SELECT 1 FROM TG_ARGV[0]::TEXT WHERE ean = NEW.ean)
THEN
UPDATE store SET price_form_factor=NEW.price_form_factor, price_unit=NEW.price_unit WHERE ean=NEW.ean;
RETURN NULL;
END IF;
RETURN NEW;
END;
$insert_or_update_lojas$ LANGUAGE plpgsql;');
DB::unprepared('
CREATE TRIGGER insert_or_update_continente BEFORE INSERT ON continente FOR EACH ROW EXECUTE PROCEDURE insert_or_update_lojas('continente');');
Right now I’m getting
relation "store" does not exist LINE 1: SELECT EXISTS (SELECT 1 FROM store WHERE ean = NEW.ean) ^ QUERY: SELECT EXISTS (SELECT 1 FROM store WHERE ean = NEW.ean) CONTEXT: PL/pgSQL function insert_or_update_lojas() line 6 at IF
EDIT:
After googling for the past day i slightly changed the if statement. IF EXECUTE format('EXISTS SELECT 1 FROM %s WHERE ean = NEW.ean', store) THEN EXECUTE ('UPDATE store SET price_form_factor=NEW.price_form_factor, price_unit=NEW.price_unit WHERE ean=NEW.ean'); RETURN NULL; END IF;
I know the format() works. However when I do EXECUTE format() I get the following error:
SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "("
LINE 8:
EXECUTE format('EXISTS SELECT 1 FROM %s WHER...
^
(SQL:
CREATE OR REPLACE FUNCTION insert_or_update_lojas() RETURNS trigger AS $insert_or_update_lojas$
DECLARE
store TEXT := quote_ident(TG_ARGV[0]);
BEGIN
-- Check if product exists
IF
EXECUTE format('EXISTS SELECT 1 FROM %s WHERE ean = NEW.ean', store)
THEN
EXECUTE ('UPDATE store SET price_form_factor=NEW.price_form_factor, price_unit=NEW.price_unit WHERE ean=NEW.ean');
RETURN NULL;
END IF;