I need to monitor the ongoing flow of data using the latest created date for a set of tables. Basically, I need to batch run SELECT MAX(z_date_creation) FROM table_schema.table_name on a set of tables I retrieve with SELECT c.table_schema, c.table_name…
The function doesn't throw any error nor does it show any return value. CREATE OR REPLACE FUNCTION login(iemail VARCHAR,ipassword VARCHAR) RETURNS TABLE(id INTEGER, name VARCHAR, lastName VARCHAR, age INTEGER, mobile VARCHAR,email VARCHAR) LANGUAGE 'plpgsql' AS $$ #variable_conflict use_variable BEGIN RETURN…
Here's the problem. create table customer ( customer_id int generated by default as identity (start with 100) primary key ); create table cart ( cart_id int generated by default as identity (start with 100) primary key ); I want to…
I want to count certain values in all tables of a schema that contain a column that can contain those values. Was hoping to use a LATERAL join to loop over all tables, but I'm running into issues: select fully_qualified_table_name,…
I'm using pgAdmin, and I want to have a simple raise notice; referring to this, I entered RAISE NOTICE 'note'; and got this error: ERROR: syntax error at or near "RAISE" LINE 1: RAISE NOTICE 'note'; The only way I…
I am writing an sql program that creates a table with columns computed in a function. The function returns the record type. This is what the header looks like: create or replace function get_items(col1 int, col2 int) returns record What…
I am new to SQL and still learning functions and triggers. I have 3 tables: PRODUCTS_BOUGHT CUSTOMER DATE PRODUCTS 3FG 2022-12-15 25 4HZ 2022-12-18 30 PRODUCTS_PRICE: DATE TYPE PRICE 2022-12-15 A 125$ 2022-12-18 B 147$ CUSTOMERS_REGISTER: CUSTOMER TYPE 3FG A…
I have a issue when I try to insert into users table. DO $$ DECLARE newId bigint; BEGIN INSERT INTO users("email","bio","status","created_by","updated_by") VALUES ('[email protected]','❄❄Haocute$$','accepted','1','1') RETURNING id INTO newId; UPDATE synces SET "version" = "version" + 1, updated_at = now(), new_id =…
I am trying to define custom function and I wanted to find how can I calculate estimated cost of that function https://www.postgresql.org/docs/current/sql-createfunction.html I tried giving different values of cost function but unable to find to find how to estimate that…
I receive a syntax error for the following: CREATE OR REPLACE FUNCTION my_function(old_num INTEGER) returns INTEGER language plpgsql AS $$ DECLARE new_num INTEGER; BEGIN CASE WHEN (old_num IN (1, 2, 3, 4)) THEN new_num = 10 WHEN (old_num IN (5,…