Let's say I have a function that returns type record: CREATE OR REPLACE FUNCTION pnh_row(myId integer) RETURNS RECORD AS $$ DECLARE myRow RECORD; BEGIN // blah blah return myRow; END; $$ language plpgsql; How can I insert and update this…
I am building a function to insert a record into an audit table every time a record is inserted, updated, or deleted in the parent table. I am struggling to insert one JSON object that is the primary keys of…
i need to get rows counts for specific columns with null and insert the same rows count output into a table with result set of the query. For this requirement, i tried with below pl/sql code block in postgres but…
I have a requirement to dynamically execute a query string. For this I have written a function which generates this query string. The function successfully returns the right query string depending upon the client name that I pass, however, I…
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(),…