for snippets

For loop in Postgres

Tagged for, loop, postgres  Languages sql
DO
$$
DECLARE
    row record;
BEGIN
    FOR row IN SELECT tablename FROM pg_tables WHERE schemaname = 'public'
    LOOP
        EXECUTE 'ALTER TABLE public.' || quote_ident(row.tablename) || ' SET SCHEMA <new schema>;';
    END LOOP;
END;
$$;

For loops with LATERAL JOIN in PostgreSQL

Tagged for, loop, postgresql, previous, quarter  Languages sql
SELECT
  *
-- initial data that we want to loop through
FROM (
  SELECT
    *
  FROM
    financials
  WHERE
    quarter = '2020-06-30'
-- for each row run this query
) current_quarter LEFT JOIN LATERAL (
  SELECT
    revenue AS previous_revenue
  FROM
    financials
  WHERE
    company_id = current_quarter.company_id AND 
    -- calculate previous_quarter by subtracting one day
    quarter = to_char(date_trunc('quarter', current_quarter.quarter)::date - 1, 'yyyy-mm-dd')::date
) previous_quarter ON true;