Thread: function likes sprintf
Hi all! Do pgsql function like sprintf() in C? I write trigger function using plpgsql. This my code: sqlcommand := textcat(''CREATE USER '',NEW.usename); sqlcommand := textcat(sqlcommand, ''WITH SYSID ''); sqlcommand := textcat(sqlcommand, NEW.id); sqlcommand := textcat(sqlcommand, ''WITH PASSWORD ''); sqlcommand := textcat(sqlcommand, passwd); INSERT INTO todo_surep_user VALUES (sqlcommand, now(), ''''); How do I replace "textcat" on another construct of language? Mik.
From: "Mikhail V. Majorov" <mik@ttn.ru> > Hi all! > > Do pgsql function like sprintf() in C? > I write trigger function using plpgsql. > > This my code: > sqlcommand := textcat(''CREATE USER '',NEW.usename); > sqlcommand := textcat(sqlcommand, ''WITH SYSID ''); > sqlcommand := textcat(sqlcommand, NEW.id); > sqlcommand := textcat(sqlcommand, ''WITH PASSWORD ''); > sqlcommand := textcat(sqlcommand, passwd); > INSERT INTO todo_surep_user VALUES (sqlcommand, now(), ''''); > > How do I replace "textcat" on another construct of language? You can use the || operator such as: sqlcommand := "CREATE USER " || NEW.usename || "WITH SYSID" ... If you are after the more advanced formatting %7.3f stuff you'll need to look at the various procedural languages (pltcl,plperl) - you could write you own function to emulate sprintf but I don't think the interface supports a variable number of parameters. - Richard Huxton
Richard Huxton wrote: > > From: "Mikhail V. Majorov" <mik@ttn.ru> > > > Hi all! > > > > Do pgsql function like sprintf() in C? > > I write trigger function using plpgsql. > > > > This my code: > > sqlcommand := textcat(''CREATE USER '',NEW.usename); > > sqlcommand := textcat(sqlcommand, ''WITH SYSID ''); > > sqlcommand := textcat(sqlcommand, NEW.id); > > sqlcommand := textcat(sqlcommand, ''WITH PASSWORD ''); > > sqlcommand := textcat(sqlcommand, passwd); > > INSERT INTO todo_surep_user VALUES (sqlcommand, now(), ''''); > > > > How do I replace "textcat" on another construct of language? > > You can use the || operator such as: > > sqlcommand := "CREATE USER " || NEW.usename || "WITH SYSID" ... > > If you are after the more advanced formatting %7.3f stuff you'll need to > look at the various procedural languages (pltcl,plperl) - you could write > you own function to emulate sprintf but I don't think the interface supports > a variable number of parameters. Thanks for guess. I use plpgsql becase this language permit to use SQL command. As I undestand I can't use SQL command from plperl or pltcl. Is it right? Mik.
"Mikhail V. Majorov" <mik@ttn.ru> writes: > I use plpgsql becase this language permit to use SQL command. > As I undestand I can't use SQL command from plperl or pltcl. Is it > right? Not so --- pltcl has "SPI" commands that let you issue SQL queries. (plperl seems not to have the equivalent functionality yet, which is a shame because it really hobbles plperl.) See the documentation at http://www.postgresql.org/devel-corner/docs/postgres/programmer-pl.htm (this is for 7.1 but it mostly applies to the 7.0 release). regards, tom lane