I’m trying to generate some sample data for the following table see below. I have functions that can generate a random DATE and timestamp within a date range, which is working fine. I want to use the MOD command to populate the active field to ‘Y’ OR ‘N’. I want to set every row …
Tag: stored-functions
Postgres comparing strings from json and table
I have the following postgres stored function: CREATE OR REPLACE FUNCTION wg.get_user( req jsonb) RETURNS jsonb LANGUAGE ‘plpgsql’ COST 100 VOLATILE AS $BODY$ DECLARE …
Function to convert units based on conversion table
There is a very simple table in the database with metric unit conversions, that looks like: I was thinking about creating a function to convert units that calculates the conversion from the table based on inputs (Unit, FromUnit, ToUnit). For example Conv_Units(0.1, ‘m’, ‘cm’) returns 1…
Unable to MAX(COUNT) and have multiple columns
I have 4 tables, EMPLOYEE, DRIVER, TRIP and TRIPLEG EMPLOYEE table has Name which I want to extract and show with MAX count, has E# which is shared with DRIVER DRIVER table has L#(licence number) which is common with TRIP TRIP table has T#(trip number) which is common with TRIPLEG I’m trying to find the…
A constraint with function that contains Select is never satisfied
I have a table like this: And I need to forbid any rent time intersection for the same car. I found that, when a function is used (example below) inside the constraint, the value it returns is different from whent it is used outside the constraint. So when I’m trying to insert data into the table, the c…
How to insert data into a String array in postgres stored procedure
I have a pseudo stored procedure as given below: CREATE OR REPLACE FUNCTION get_data() RETURNS void AS $$ DECLARE rec_new RECORD; querystring TEXT[]; cursor_file CURSOR FOR …
Stored function not returning a table in SQL Server
I’m trying to create a stored function called func_GetMenuItemsForMenu. For this function, I need to pass the ID of the menu to it. The function will find all MenuItems that are on the menu. It will …
How to use variables in “EXECUTE format()” in plpgsql
I want to update a column in table stats with the specific column being a parameter, then return the updated value of that column [only has 1 row]: CREATE FUNCTION grow(col varchar) RETURNS integer …