0

I currently have a function:

Create Or Replace Function pvt()
    RETURNS void
    LANGUAGE 'plpgsql'
AS '
declare
    sqlColumn varchar;
    qr varchar;
    columnlist varchar;
Begin
sqlColumn= ''select distinct D.sys_cat from (select Row_Number() Over (Partition By Project,Date_ Order By System_) as sys_cat From your_table) D order by D.sys_cat;'';
qr=''prepare pvtstmt as Select D.Project,D.Date_,'';

For columnlist In EXECUTE sqlColumn  
   Loop
      qr=qr || ''
      Max(Case When sys_cat=''|| chr(39) || columnlist || chr(39) ||'' Then System_ ||'' ||chr(39)||''-''||chr(39)||''|| Result_ Else '' ||chr(39)||chr(39)||'' End) As System'' ||  columnlist ||  '' , '';
   END LOOP;
   
   qr=substr(qr, 0, length(qr) - 1);
   qr=qr || ''From 
   (select *, Row_Number() Over (Partition By Project,Date_ Order By System_) as sys_cat From your_table) D Where D.Project='' || chr(39) || ''Proj1'' || chr(39) || ''Group By D.Project,D.Date_ Order By D.Project,D.Date_'';
   
Deallocate All;
EXECUTE qr;
End;
'

I run these two commands which produces a table

Select pvt();
Execute pvtstmt

I am wondering if there is any way to store the results of this table temporarily, so I can query it further. Thank you!

Improve this question
9
  • @nbk, hey thanks for the reply! could you perhaps clarify which select you are referring to and how to insert the results, I tried a few different functions but I kept getting errors. Thank you! Commented Aug 1, 2022 at 15:39
  • i am sorry posgres doesn't allow create table in the statement, the documentation wasn't that clear on that point over statements Commented Aug 1, 2022 at 16:51
  • okay no worries, if I wanted to create an "Overall" column where it stores "PASS" if all of the Systems contain "PASS" and "FAIL" if one or more of the Systems contains "FAIL", do you know how I could do that within this query? Commented Aug 1, 2022 at 17:01
  • You can save result as variable, but remember the rule is the result from select query MUST have one column and one row only. So make sure you serialize it as array value or JSON value. stackoverflow.com/questions/44272446/… Commented Aug 1, 2022 at 17:53
  • Make your life and embrace Dollar quoting per plpgsql structure and plpgsql quoting. Commented Aug 1, 2022 at 18:38
Related questions
2
Dynamic SQL PostgreSQL
0
save result from dynamic query
4
dynamic query postgres
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.