function likes sprintf

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