Thread: First plpgsql Script

First plpgsql Script

From
"Van Ingen, Lane"
Date:
I am setting up my first trigger, and writing my first plpgsql script.
 
I am finding that once I read information into a ROWTYPE or RECORD variable, I want to manipulate the
data a bit more before leaving the function. But a lot of the stuff  data manipulation features I want to do appear to
onlybe available to SQL statements, rather than being able to apply some functions like what is available in SQL on
storedvariables.
 
 
So far, the only way I have been able to do what I want to do is to select data from a table, assign it to a variable
manipulatea little bit, then stuff it into a single temp record table so I can process the data further by applying
somemore SQL functions against it.
 
 
The only plpgsql commands available that I am aware of  are assignment commands, loop-related commands, logical tests
(if-then-else),and the like. Perhaps I was hoping that plpgsql has more capability than it does. What I was
specificallylooking for is to be able to do things like date arithmetic, manipulate stored arrays, accumulate totals,
etc.against stored variables while assigning the result to yet another stored variable.
 
 
Can I do this sort of thing, without having to resort to a different language? Or have I missed something? All of the
documentationin the manual (even UDFs) appear to be oriented to using SQL, especially SELECT statements.
 

Re: First plpgsql Script

From
Tom Lane
Date:
"Van Ingen, Lane" <lvaningen@ESNCC.com> writes:
> The only plpgsql commands available that I am aware of  are assignment commands, loop-related commands, logical tests
(if-then-else),and the like. Perhaps I was hoping that plpgsql has more capability than it does. What I was
specificallylooking for is to be able to do things like date arithmetic, manipulate stored arrays, accumulate totals,
etc.against stored variables while assigning the result to yet another stored variable. 

You seem to be supposing that assignment is incapable of doing any
computation :-(.

You might try looking at the examples that show how to duplicate
Oracle's instr() functions:
http://www.postgresql.org/docs/8.0/static/plpgsql-porting.html#PLPGSQL-PORTING-APPENDIX

            regards, tom lane