Обсуждение: 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