I have requirement where bash script will pass on the string consisting tables names to anonymous PL/pgSQL block which will process the tables if they exists in the Postgres db. Below is the PL/pgSQL code assuming given_tables variable has the…
I am trying to build a function to return the count of days past due where the customer was less than 3 days late. Using the dvdrental database provided by PostgreSQL. It seems as though it's returning 6 for every…
I am trying to write a stored procedure to get an employee by input ID. In SQL Server, it looks like this: CREATE PROCEDURE [dbo].[GetbyID] (@ID NVARCHAR(50)) AS BEGIN SELECT * FROM dbo.Employee AS M LEFT OUTER JOIN dbo.Position AS…
I created person table, then inserted 2 rows into it as shown below: CREATE TABLE person ( id INT, name VARCHAR(20), age INT ); INSERT INTO person (id, name, age) VALUES (1, 'John', 27), (2, 'David', 32); Then with an…
I'm trying to create 3 arrays, iterate through each, and insert records depending on the value of those iterations. My code is as follows: do $$ declare days integer[]; times time[]; durations integer[]; begin days := array[1,2, 3, 4, 5,…
I have a table named member. I want to create a function that inserts a new email if it doesn't exist in the table, and then return a boolean. CREATE OR REPLACE FUNCTION add_member(member_email TEXT) RETURNS BOOLEAN AS $$ BEGIN…
I'm using this statement to retrieve the boolean value of a column in order to set a session_variable with that value: This is the statement: select case when exists( select 1 from group_views where cwgv_group=vGroup and cwgv_table=0) then '1' else…
I'm trying to create a function in PostgreSQL to get data from a table in a dynamic way, but if i update the structure of "my_table_2023", it will raise the error because the structure doesn't match: ERROR: structure of query…
I have the following tables which i want to write a function to return data from Table Definitions create table device ( deviceid uuid not null constraint pk__device__49e123311461d246 primary key, serialnumber varchar(255), productcode varchar(255), description varchar(255), softwareversion varchar(255), build varchar(255),…
I am trying to implement a Postgres function with optional parameters. I tried a stable PL/pgSQL function with required parameters, splitting cases along user input in an IF construct. Here is a simplified example: Setup: CREATE TABLE IF NOT EXISTS…