Skip to content

Instantly share code, notes, and snippets.

@edoardoc

edoardoc/plpgsql_query_function_template.sql

Created December 10, 2021 16:53
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save edoardoc/2d481a3e54cff41a478ac21b799e3355 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save edoardoc/2d481a3e54cff41a478ac21b799e3355 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. edoardoc created this gist Dec 10, 2021.
    41 changes: 41 additions & 0 deletions plpgsql_query_function_template.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,41 @@
    -- PL/pgSQL Template to run inline a complicated function and get results in console
    DO $$
    DECLARE
    -- ADD QUERY DECLARATION HERE:
    filteringIdOfSomeKind UUID;

    -- ADD QUERY DECLARATION HERE -- END!

    t timestamptz;
    target record;

    BEGIN
    t := clock_timestamp();
    filteringIdOfSomeKind := '8cae6ff8-b532-4749-94b4-ffc51c933b86';

    RAISE NOTICE 'HELLO THERE filteringIdOfSomeKind = %', filteringIdOfSomeKind;
    RAISE NOTICE '----------------------';
    FOR target IN

    -- ADD CORE PART OF QUERY

    SELECT random()*100 as theId, * FROM generate_series(1,5)


    -- ADD CORE PART OF QUERY -- END! (REMOVE LAST ;)


    LOOP
    -- the only way to address the output is to fetch single fields as this:
    -- RAISE NOTICE 'fieldNameNo1= %', 'target.fieldNameNo1';
    -- RAISE NOTICE 'fieldNameNo2= %', 'target.fieldNameNo2';
    -- ...
    -- or just print the whole thing TODO: add some formatting
    RAISE NOTICE '%', target;

    END LOOP;
    RAISE NOTICE '----------------------';
    RAISE NOTICE 'END BLOCK time spent=%', clock_timestamp() - t;


    END; $$;