I'm new to Postgres, so I'm sorry if this is overly simple. But I've searched for ages, and can't find a solution for my dynamic-sql problem.
I have one table that contains the list of columns in a second table (and some metadata about these columns) that relate to a particular dataset.
I need to get that dataset, preferably with one stored procedure/function/query.
So far I've created a function (with one input parameter) that returns the necessary select string to extract the records that I want. But I can't find a way to automatically run this statement.
I can't write a function to return a table, because I can't define the table structure beforehand. Or am I just doing something wrong?
This will ultimately be called from a php statement, so the results can be displayed in a webpage, so if I can't do it on the server, I can always have the php code first return the sql string and then execute it separately. But it would be neater if I could do this in a single transaction.
I don't need to change any records, just display them.
I'm running Postgres 9.6.
This is the function I wrote to extract the string of columns and their display names from a table called t_fileattributenames that I want to extract from the table that contains the file attribute data:
CREATE OR REPLACE FUNCTION pdls.getattstring( this_dsid bigint) RETURNS text LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$ declare isdone int; resultarray text; strcolnames text; whichfileid int; mviews record; mystring text; i int; begin i=1; FOR mviews IN SELECT col_name,fieldname,fileid FROM pdls.t_fileattributenames where dsid=this_dsid ORDER BY col_order LOOP resultarray[i]=mviews.col_name || ' AS "' || mviews.fieldname || '"'; whichfileid=mviews.fileid; i=i+1; END LOOP ; strcolnames := array_to_string(resultarray,',',null) ; if strcolnames is not null then strcolnames='dsid, ' || strcolnames; else strcolnames=''; end if; return strcolnames; end $BODY$;
Let's call the output $rtnstring.
With an example input of
dsid=9 the output will look like this:
$rtnstring= 'dsid, str1 AS "Researcher code",str2 AS "Filename",int1 AS "Have File?", int2 AS "Pump number",int3 AS "Test number",int4 AS "Test number order", int5 AS "BEP",int6 AS "Flowrate in m3/hr",dbl1 AS "Velocity in m/s", dbl2 AS "%BEP",dbl3 AS "Efficiency",dbl4 AS "Power to pump in kW", dbl5 AS "Calculated Efficiency",dbl6 AS "NPSHa",dbl7 AS "NPSHratio", dbl8 AS "NHSPR",dbl9 AS "Total head in m",dbl10 AS "Suction pressure in kPa", dbl11 AS "Vapor pressure in kPa",dbl12 AS "Cavitation number"'
That bit works fine.
What I really want to do is something like this (Excuse the php format):
"select " . $rtnstring . " from pdls.t_fileattributes inner join pdls.t_datafiles on t_fileattributes.fileid=t_datafiles.fileid where t_datafiles.dsid=" . $dsid)
When I manually run the query by cutting and pasting the output from the first query, I get the data I want.
Can I do both queries one SQL step? I don't know what number of columns or their types I will have until I run the first query, but as they will all be rendered on a webpage, they can all be converted to text if necessary.
If not, I'll just have PHP run the two steps separately.