dynamic SQL parameters in PL/PgSQL functions
It pays to read documentation.
From PostgreSQL 8.3 to 8.4 there was one interesting addon for plpgsql: ability to pass parameter values directly into EXECUTE.
Of course depesz wrote about it. And I did read it; But still (by routine) I was using something like:
sql_query := 'SELECT foo FROM bar JOIN baz USING (barbaz) WHERE baz.id = ANY (__PARAM__::integer[])';
sql_query := replace(sql_query, '__PARAM__', quote_nullable(my_param);
EXECUTE sql_query INTO my_foos;
While it can be written in more elegant and error-prone way:
EXECUTE 'SELECT foo FROM bar JOIN baz USING (barbaz) WHERE baz.id = ANY ($1)'
INTO my_foos
USING my_param;
iterate over a table in PostgreSQL vs SAS
I have struggled how to achieve something like this in SAS.
(you can read it as pseudocode but it's real PL/PgSQL):
DO $this$
DECLARE my_table name;
BEGIN;
-- iterate over table names
FOR my_table IN SELECT nazwa FROM tabele
LOOP
sql := 'CREATE TABLE XXX_backup AS SELECT * FROM XXX';
sql := replace(sql, 'XXX', my_table);
execute sql;
END LOOP;
END;
$this$ language plpgsql;
After consulting a collegue [thx Ludwik] and exploring this helpful doc:
Performing Multiple Statements for Each Record in a SAS® Data Set, tadam:
%MACRO COPYTABLES(SCANFILE,SCANFIELD);
*get number of tables;
DATA _NULL_;
IF 0 THEN SET &SCANFILE NOBS=X;
CALL SYMPUT('RECCOUNT',X);
STOP;
RUN;
*iterate;
%DO I=1 %TO &RECCOUNT;
*get table name;
DATA _NULL_;
SET &SCANFILE (FIRSTOBS=&I);
CALL SYMPUT('TABLENAME',&SCANFIELD);
STOP;
RUN;
*copy;
DATA &TABLENAME._backup;
SET &TABLENAME;
RUN;
%END;
%MEND COPYSETS;
%COPYSETS(tabele,nazwa);
When you switch from procedural coding to macro coding, you have twist your brain upside down...
I had to read a white paper (SAS docs lacking examples) and scratch my head for a moment before writing this simple procedural loop in SAS 4GL.
Now tell me that SAS isn't weird ;-)