I am trying to create simple trigger to ensure the "from" or "to" (database usernames) is equal to current_user, then can delete the record. But its always raises an exception. Table Schema: CREATE TABLE policy_test ( id serial not null,…
total nube in PL/PGSQL. I would like to define an array of strings and then used that in my SELECT... WHERE In statement but can't seem to get it to work, help appreciated. DO $$ DECLARE testArray varchar[] := array['john','lisa'];…
I am writing a PostgreSQL trigger function that will allow me to do an ETL transformation on a data. I have a table with fields (id, price, EFF_FROM, EFF_TO), and if I only insert (id, price, EFF_FROM), EFF_TO needs to…
I'm stuck with a plpgsql syntax error. Here is the error message : ERROR: syntax error on or near "LANGUAGE" LINE 30: LANGUAGE plpgsql; I solved a previous syntax error and after I changed my code I got a new…
I have several views on the same table containing overlapping data. By dynamically retrieving the views from the information_schema and unioning them I can create a text query result. However I still need to manually run that query to get…
I have the following data: sale_id sale_date installments total_value 1 2023/01/01 2 100.0 1 2023/02/01 2 0.0 1 2023/03/01 2 0.0 1 2023/04/01 3 90.0 1 2023/05/01 3 0.0 1 2023/06/01 3 0.0 2 2023/01/01 1 100.0 I need to…
This function is a trigger that restricts grouping by series type when grouping posts. CREATE OR REPLACE FUNCTION post_is_publish() RETURNS trigger LANGUAGE PLPGSQL AS $body$ BEGIN WITH psid AS ( SELECT id FROM post_series WHERE id = NEW.post_series_id ) ,p…
I've got some PL/pgSQL code like this: DECLARE v_schema_name pg_catalog.pg_namespace.nspname%type := 'my_schema'; v_table_name pg_catalog.pg_tables.tablename%type := 'my_table'; v_column_name pg_catalog.pg_attribute.attname%type := 'my_column'; v_new_type TEXT := 'DECIMAL(16, 12)'; BEGIN -- Omitted other code using v_new_type EXECUTE format( 'ALTER TABLE %I.%I ALTER COLUMN %I…
Postgresql trigger function throws exception (failed jsonb parsing) How can i fix it? first table CREATE TABLE document_templates ( id SERIAL PRIMARY KEY, name VARCHAR(255) NOT NULL, created_at TIMESTAMP NOT NULL, version INT NOT NULL, fields JSONB NOT NULL );…
In this code, when I run: CREATE TABLE IF NOT EXISTS setup_workspace_result ( path TEXT, workspace_id BIGINT ); DROP FUNCTION IF EXISTS public.setup_workspace; CREATE OR REPLACE FUNCTION setup_workspace(refresh_token TEXT) RETURNS setup_workspace_result AS $$ DECLARE user_email TEXT := ((current_setting('request.jwt.claims'::text, TRUE))::JSON ->>…