Writting PL/pgSQL in the main body

[tech4science]

I want to write a loop using PL/pgSQL to perform an incrementing loop that will substitute the numbers in the query. PostGreSQL documentation shows writting PL/pgSQL code inside function definitions, but I have no need to create a function.
Can I write PL/pgSQL code directly alongside other queries?
If I have to create a function, how can I run a function by itself without placing it inside a query?

DECLARE
year INTEGER := 0;
BEGIN
LOOP
EXECUTE ''

EXECUTE ''
-- if source cost data is a comment or other invalid cost value, then copy data to destination comment field
UPDATE master
SET
fnote'' || year || '' = baseline.fyear'' || year || '',
fyear'' || year || '' = NULL
FROM baseline
WHERE
master.ckt_id = baseline.ckt_id AND
baseline.fyear'' || year || '' !~ ''''(^[0-9]+\.?[0-9]*$)|(^\.[0-9]+$)''''
'';

year := year + 1;
EXIT WHEN year = 10;
END LOOP;
END;
' LANGUAGE 'plpgsql';

 

[Leland123]

Hi tech4science,

You might take a look at the PostgreSQL cookbook pages. There are a number of functions that may give you some ideas. The PostgreSQL cookbook pages can be found at the following link:

http://www.brasileiro.net:8080/postgres/cookbook/

Also, you might consider placing you incrementing date loop inside a front-end programming language like perl, php, python, C/C++, VBA, FoxPro, etc and execute it programmically from within an application.

LelandJ Leland F. Jackson, CPA
Software - Master (TM)

https://www.smvfp.com

Nothing Runs Like the Fox