Re: plpgsql grief - Mailing list pgsql-sql

Tcl is my bread and butter but, coincidentally, I have just started considering
pl/tcl 2 days ago as the choice for server side pg programming.  I do it in
microsoft t-sql right now, and plsql is pretty close to that.  However, tcl is
like English to me, so I think I will go that way unless someone can tell me why
I shouldn't.

I found that the docs are actually pretty excellent, although brief, on pl/tcl.
Tcl is a strange animal, so you will have to get used to 'lists' and occasional
unwanted/unexpected variable substitution until you get the hang of it.  But if
you scan the docs you will find some interesting stuff.  It seems they have
provided everything you and I will ever need, we just need to figure out how to
use it.

The docs are in USER versus PROGRAMMER.  Go figure.

There is a newsgroup called 'novice' that is useful from time to time.  I don't
know why the server has had such availability issues the last couple days, it is
usually very good.

Have fun,

Ian

rob wrote:

> <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