Re: plpgsql grief - Mailing list pgsql-sql

<snip>
> 
> I feel your pain;^)
> 
> Here is the text of a post from Tuesday... I think it answers your question
> which is that you cannot do variable subsititution for table or field names
> inside procedures.  This is not a Postgres specific limitation, MS SQL
> Server has the same issue.
> 
> > >>>>> "DR" == David Richter
> >  writes:
<snip>
Ya know, i already read this one. That's what got me on to 7.1 and using
EXECUTE :)

ARgh! Apparenty I've opened up a real can of worms with wanting cool
general functions, with a bit of dynamism and business logic.
However....

OK dumped pl/pgsql, thinking pl/tcl is more my bag (didn't fancy
recompiling perl to get the shared lib, and didn't want to waste much
time struggling to somehow see if PHP could be used, since PHP is my
current 'main' lang). Pl/tcl supports dynamic queries - great !. However
it introduced it's own little wrinkles. Now for starters I've no
knowledge of tcl, however doing my job means learning loads of
exteranous crap, and what another lang... I reckon I can do it, just
need a little help. So here goes.

found out some things too - trigger functions must return opaque (ok not
100% on what opaque is, but I'm not worrying about that just yet), also
can't have parameters in the function name - odd, but this lead on to -
how the hell would you pass the parameters to the func anyway if it's
attached to a trigger - like INSERT - and you merely type the SQL :
 insert into tablename values bla blabla ;

and invoke the trigger, which inturn invokes the function ... erm there
- wheres the transport to passing the parameters (sorry i know my
programmers lingo isn't 100%).

here's my tcl'ed function, which i attached to my main table on INSERT.
It's supposed to log the action to another table passed to it - the idea
is i've generalised some logging tables to various 'main' tables, and
these functions are supposed to record various db and system level
events. The function represents a bit of copying and playing - duno what
TPL is however it works. I'll address further issues, error checking,
processing etc when I've got it actually doing something :

create function update_trans () returns opaque as ' spi_exec -array TPL "insert into $1 (objid, objtbl, et, event,
time,
reason, owner) values (\'$2\', \'$3\', \'$4\', \'$5\', now(), \'$6\',
\'$7\')"
' language 'pltcl' ;

which works !! well, gets invoked however it doesn't know what 'text'
is. Not sure where that got picked up from, but it obviously didn't work
- however the insert did. Now I found out there's a parameter array
tgargs or something, but how does this get set ? How does it then get
accessed in the function ?

OK, being the resourceful chap I am (relatively) how about this, a
slight diversion. Why not just make up some function which do the main
insert, business logic, event logging stuff explicity and sack off doing
sql inserts/update etcs.

(can't return opaque here, but that's no bother, i think (hope))

New function

create function update_trans (text, int4, text, text, text, text, text)
returns boolean as ' spi_exec -array TPL "insert into $1 (objid, objtbl, et, event, time,
reason, owner) values (\'$2\', \'$3\', \'$4\', \'$5\', now(), \'$6\',
\'$7\')" return1
' language 'pltcl' ;

then call these from my code like

select update_trans (bla, 1, bla, bla blabl) ;

which works also. I get to pass all the parameters i want, and have full
control over execution. OK this looses part of the reason for doing this
in the first place - tracking people who side track the app code by
modifying the db directly, however since noone should be doing that
anyway, no problem. (note should). 

Again apologies for the verbose message - i feel the 'fuller picture' is
more useful in the long run, rather than diconnected questions. Well it
is to me when I'm on your side of the fence.

Thanks for the reply BTW. Oh, and why is this news group only accessible
during late afternoon from 3pm'ish GMT. I'm access it from the UK. All
morning, for two days, I couldn't get on - server busy errors.

Oh and before I forget - over several months of news group postings
there has been the recognision of the need for examples for us newies,
and some mention of people compiling various docs for just such a
purpose - anyone get anywhere with any of these, as they were several
months ago. I'm certainly gaining some real gotcha type info on all of
this :)

Now I've had it. Burned out. So off to the pub and henceforth become as
drunk as a skunk !

Regards

Rob