I select data from temporary table: CREATE TEMPORARY TABLE IF NOT EXISTS "tmp" ( "fuel_type" TEXT, "litres" NUMERIC(8,2), "is_rent" BOOLEAN ); insert into tmp values ('petrol', 10, False); insert into tmp values ('petrol', 20, False); insert into tmp values ('diesel',…
I created a function that returns all values that meet the condition specified and tried specifying int[] as the result type of the function. create or replace function mults_of_3_5(id int) returns int[] language sql as $$ SELECT ValuesUnder1000 FROM Mults…
There are multiple historical rows present for a customer in customer table and a id has been assigned which might have changed overtime Name ID Date Abhishek 1 23-08-2023 Abhishek 1 03-08-2023 Abhishek 2 17-06-2023 Abhishek 3 09-10-2022 Seema A…
My first post in Stackoverflow. I am trying to learn Postgresql (12) "on the job" and seem to be stuck on a rather simple issue. I have a simple database with 12 rows. In one column (int) there is a…
It's my custom type. CREATE TYPE salary_info AS ( max_salary numeric, min_salary numeric ); CREATE FUNCTION test_1(city varchar(15)) RETURNS salary_info AS $$ SELECT MAX(salary) AS min_salary, MIN(salary) AS max_salary FROM employees WHERE city = test_1.city; $$ LANGUAGE SQL; SELECT test_1('New…
I have a query that selects the rows from joined table as an array using ARRAY_AGG() function. select entity_number, ARRAY_AGG('{"property_id":"'||property_id||'","value":"'||value||'"}') entity_properties from entities join entity_properties on entities.id = entity_properties.entity_id where entities.id in ( select entity_id from entity_properties where value =…
Here is the code of the (My)SQL function (it works outside of the function), but I can't manage to save it as a function for further reuse... This is an example of the working query: SELECT ( SUM(Items_Available * Store_Sales)…
I'm trying to get something I thought should be relatively simple (it works in Oracle and MySQL). The PostgreSQL fiddle for the code below is available here - just change the server to check out the others. Very simple test…
I am changing a db function and I need to add a query for items matching their category id with any items from an array i give. Example input: let categories = ['10272111', '165796011', '3760911', '9223372036854776000','3760901','7141123011'] Part of db function:…
I have sql function that I need to migrate to phpmyadmin in order for my code to work. I make migration where I insert code for sql function and then I do php artisan migrate:fresh --seed it successfully completes and…