I expected the function call "expensive" to be called only once, but it appears to be called for every row in "children". Can the CTE/query be tweaked to only call the function once and have that value returned for every…
I want to be able to call set_config to set a configuration parameter within the same transaction/scope as an INSERT statement. There is a function triggered AFTER the insert which reads from this configuration parameter. I need to accomplish this…
Given a directed acyclic graph shaped like this: "Branch A" "Branch B" branch-a-head-id branch-b-head-id --- | | ^ id-Z id-C | | | | id-Y id-B | | | "Branch A" id-X id-A and | | "Branch B" branch-a-first-id branch-b-first-id…
I have a following SQL running in PostgreSQL v13.10: WITH stuckable_statuses AS ( SELECT status_id FROM status_descriptions WHERE (tags @> ARRAY['stuckable']::varchar[]) ) SELECT jobs.* FROM jobs WHERE jobs.status = ANY(select status_id from stuckable_statuses) And it is running really slow while…
we want to "fill in the gaps" of the record_ids and fill in missing values with the most recent available value. If there is no value available, use the first available value instead. The result should look like: category record_id…
I'm working with AWS RDS MySQL and using MySQL Workbench to develop the queries before moving them into the Lambda integration function for my HTTP API on AWS API Gateway. I created this query: use prod_esports; WITH muuid AS (select…
I wrote the below query with a CTE attempting to return all the dates on which daily transactions exceeded the average of all daily transactions; however, MySQL keeps returning an error message for something around line 1. I've attached a…
I have the following query: WITH matching_data as ( select * from consultants_matching_data_for_project(6001) ) select id, matching_data.city_matching from consultant_profiles LEFT OUTER JOIN matching_data on matching_data.consultant_profile_id = consultant_profiles.id; Is there any other way to join with matching_data result without using a…
I am trying to write a query where I select records by some condition, update one column of those records and then return them, all in one transaction. I have tried two options. First with CTE: BEGIN; WITH unwatched_rows AS…
sorry if this is so obviously easy but I am new to this! I am trying to add another WITH statement to a query that works perfectly fine, but breaks when I edit it. The query I am trying is:…