i have a table called stock_holdings which does not have data related to account_id and ticker_cd (actually it is empty): select qty,total_amount from stock_holdings where account_id=1 and ticker_cd='XYZ'; qty | total_amount -----+-------------- (0 rows) however when i run the following…
I want to query a table for where the number of identical values in one column is equal to the number distinct values in another column. I'm not sure how to phrase that better, so I think it best to…
In the case that I have the same example data as in this question and additionally declare the following two functions: CREATE OR REPLACE FUNCTION example.markout_666_example_666_price_table_666_price(_symbol text, _time_of timestamptz, _start interval, _duration interval) RETURNS float8 LANGUAGE sql STABLE STRICT PARALLEL…
I want to write a Postgres function that looks up the value of a column in a given table. Example setup: CREATE SCHEMA IF NOT EXISTS example; CREATE TABLE example.price_table ( symbol text NOT NULL, time_of timestamptz NOT NULL, price…
A personal identity codes consist of a string of numbers that indicates the individual’s date of birth, an individualized string, and a control character. Example: 150600A905P • 150600 = Date of birth • A = the character in the middle…
I have a stored procedure which attempts to write to two tables, related via a foreign key constraint. Slightly simplified table definitions: CREATE TABLE station_event ( station_code VARCHAR(3) NOT NULL, user_id INTEGER NOT NULL, event_dtm TIMESTAMPTZ NOT NULL DEFAULT now(),…
I would like to create a function that given a value of any type, a dimension of the one-dimensional array and the column to set, will return a one-dimensional array filled with null values except for one given value at…
I am working in supabase postgres and wrote multiple postgres functions DROP FUNCTION IF EXISTS setup_auth_user; CREATE OR REPLACE FUNCTION setup_auth_user( email_value TEXT, name_value TEXT, password_value TEXT, wallet_address_value TEXT, private_key_value TEXT ) RETURNS user_info AS $$ DECLARE updated_user_info user_info; BEGIN…
I created an immutable function as below: CREATE OR REPLACE FUNCTION GetCurrentDate() RETURNS DATE AS $p$ BEGIN RETURN CURRENT_TIMESTAMP::DATE; END; $p$ LANGUAGE plpgsql IMMUTABLE SECURITY DEFINER; And I use this in a UNIQUE INDEX's WHERE clause like: CREATE UNIQUE INDEX…
I have below plpgsql function - CREATE OR REPLACE FUNCTION public.end_to_end_query(part_id integer) LANGUAGE plpgsql IMMUTABLE AS $function$ DECLARE query_pw varchar := format( $dynsql$ ALTER TABLE metric_events_%s SET (parallel_workers = 16) $dynsql$, part_id ); BEGIN RAISE NOTICE 'Value: %', query; QUERY…