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…
I wrote the SQL Server stored procedure usp_getrowcounts, shown below, that dynamically gets a row count from each table, as tables come and go, and need the equivalent dynamically generated and executed PL/SQL to work in PostgreSQL. However, working with…
I'd like to assign type timestamp without timezone to all columns name created_at at my database scheme. I'm trying to run this query: DO $$ DECLARE table_name text; BEGIN FOR table_name IN SELECT table_name FROM information_schema.columns WHERE column_name = 'created_at'…
I have a table with millions of text documents in a Postgres 14.7 DB. When I try to generate a GIN index I get this error: create index idx__txt_document__doc_text__gin on txt_document using gin(to_tsvector('simple', doc_text)); ERROR: string is too long for…
I am using PostgreSQL 15 and have the following two statements in PL/pgSQL: -- Statement 1 SELECT to_json(T) FROM (SELECT * FROM category WHERE category.id = 43) T) -- Statement 2 SELECT json_build_object('products', JSON_AGG(T)) FROM (SELECT * FROM product WHERE…
enter image description here What is causing the error here? If I'm not confusing anything, everything is done as it should CREATE FUNCTION convert_temperature(temperature real, current_scale varchar DEFAULT 'Fahrenheit') RETURNS real AS $$ BEGIN IF(current_scale = 'Celsius') THEN RETURN temperatue*1.8+32…