Im building parameterised sql using pgp and wrapping the generated sql inside DO $$ END $$. Problem is when I try saving password (varchar) some of the password hashes contain $$ which causes issues since the psql is inside DO…
I am testing my Spring Boot API with a test class (FoodE2eTest) and I have a separate SQL script file (setup-test-schema.sql) that I want to run before the tests start to set up schema, tables, and data. I am using…
I need to create a PostgreSQL PL/pgSQL code capable of simulating a simple transfer of money from one account to another. So here is my code: DROP TABLE clients, accounts, transactions; CREATE TABLE IF NOT EXISTS clients ( id int…
I need to enter value for direction of sort in a function, but how to do it? Tried like that, but this doesn't work of course. create or replace function invest_ideas (order_by_direction varchar default 'desc') create or replace function invest_ideas…
I am trying to pass a date to a function in postgres. Here is my code: CREATE OR REPLACE FUNCTION public.path_optimization(input_date date) RETURNS TABLE(area_id_ uuid, result_uuids text[]) LANGUAGE plpgsql AS $function$ DECLARE r RECORD; BEGIN -- Iterate through the records…
I have tried to select a list of databases and select all tables from each database, ordering the first ten tables by their size - including their indexes- through PL/pgSQL, but I have had a problem to join the query…
I'm new to SQL and don't think I understand how to write if else statements. The following code block produces a syntax error at the if statement. create or replace function weight_from_days_ago (days_ago int, difficulty int, user_level int, root_score int)…
CREATE OR REPLACE FUNCTION display_columns_data(table_name varchar, columns_to_display varchar[]) RETURNS TABLE (result_row json) AS $$ BEGIN RETURN QUERY EXECUTE 'SELECT ' || array_to_string(columns_to_display, ',') || ' FROM ' || table_name; END; $$ LANGUAGE plpgsql; SELECT * FROM display_columns_data('employees', ARRAY['first_name', 'last_name']); Detail:…
Is there a way to iterate through individual fields of OLD records in a Postgres plpgsql trigger? I want to write a trigger function that logs all the changes that were made to the particular fields of a record. For…
I have the following SQL code: SELECT dip || ' ' || ( SELECT lib1 || ' ' || lib2 FROM bds WHERE dip_s = dip ) AS label, COUNT(dip) AS freq FROM current_table WHERE v_deg = '3' GROUP BY…