Question 89937 in PostgreSQL Question posted in
The official documentation can be found here.

Subquery as string to new query in postgresql

I have this working static query:

SELECT 
    companyname,
    companyid,
    incorporationcountryid,
    periodEndDate,
    filingdate,
    AVG(dataitemvalue) FILTER (WHERE menmonic = 'AT') as "AT",
    AVG(dataitemvalue) FILTER (WHERE menmonic = 'COGS') as "COGS"
FROM
    aaa3
GROUP BY 
    companyname, companyid,
    incorporationcountryid, periodEndDate, filingdate
ORDER BY 
    companyname, periodEndDate

Now, I’d like to incorporate all mnemonics, not just ‘AT’ and ‘COGS’ and adjust my query dynamically. I’m relatively new to SQL, but I managed to come up with this query, that gets me all unique mnemonics in my table and creates my desired query as a string:

SELECT 'SELECT companyname,companyid,incorporationcountryid,periodEndDate,filingdate,' 
|| STRING_AGG(DISTINCT CONCAT('AVG(dataitemvalue) FILTER (WHERE menmonic = "', menmonic,'") as "',menmonic,'"'),',') 
|| ' FROM aaa3 GROUP BY companyname, companyid, incorporationcountryid, periodEndDate, filingdate ORDER BY companyname, periodEndDate;'
as menmonic FROM aaa2;

enter image description here

Q: Can I turn this string into a query?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I was able to come up with a compact solution myself:

    DO $$
DECLARE
  menmonic_list text[];
  query text;
  menmonic_unique text;
BEGIN
  SELECT array_agg(DISTINCT menmonic) INTO menmonic_list
  FROM aaa3;

  query := 'SELECT companyname, companyid, incorporationcountryid, periodEndDate, filingdate';
  FOREACH menmonic_unique IN ARRAY menmonic_list
  LOOP
    query := query || ', AVG(dataitemvalue) FILTER (WHERE aaa3.menmonic = ''' || menmonic_unique || ''') AS "' || menmonic_unique || '"';
  END LOOP;
  query := query || ' FROM aaa3 GROUP BY companyname, companyid, incorporationcountryid, periodEndDate, filingdate ORDER BY companyname, periodEndDate';

  EXECUTE query;
END $$;

  2. 0

    The first idea that comes up in mind would be to store your string into a text variable and then execute it as a dynamic query using EXECUTE (see the manual).

    But then comes the issue : the query to be executed has a variable number of columns depending on the number of menmonics in table aaa2.

    So the problem is a bit more tricky and one possible solution is to create dynamically a composite type menmonic_list corresponding to the list of menmonics and then to use the jsonb_populate_record and jsonb_object_agg standard functions :

    Creating the composite type menmonic_list by trigger each time a row is inserted or updated or deleted from table aaa2 :

    CREATE OR REPLACE FUNCTION aaa2_AFTER_INSERT_UPDATE_DELETE ()
RETURNS trigger LANGUAGE plpgsql AS $$
  DECLARE
    str text ;
  BEGIN
    SELECT string_agg(DISTINCT quote_ident(menmonic) || ' numeric', ',') 
      INTO str
      FROM aaa2 ;
    DROP TYPE IF EXISTS menmonic_list ;
    EXECUTE 'CREATE TYPE menmonic_list AS (' || str || ')' ;
    RETURN NULL ;
  END ; $$ ;

CREATE OR REPLACE TRIGGER aaa2_AFTER AFTER INSERT OR UPDATE OF menmonic OR DELETE ON aaa2
  FOR EACH statement EXECUTE FUNCTION aaa2_AFTER_INSERT_UPDATE_DELETE () ;

    The final query is :

    SELECT a.companyname,a.companyid,a.incorporationcountryid,a.periodEndDate,a.filingdate,
  (jsonb_populate_record(null :: menmonic_list, jsonb_object_agg(a.key, a.value))).*
FROM
( SELECT companyname,companyid,incorporationcountryid,periodEndDate,filingdate,
  menmonic AS key, AVG(dataitemvalue) AS value
  FROM aaa3 
  GROUP BY companyname, companyid, incorporationcountryid, periodEndDate, filingdate, menmonic
) AS a  
  GROUP BY a.companyname, a.companyid, a.incorporationcountryid, a.periodEndDate, a.filingdate
  ORDER BY a.companyname, a.periodEndDate;

    see the test result in dbfiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search