Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit7504870

Browse files
committed
Add new SQL function, format(text).
Currently, three conversion format specifiers are supported: %s for astring, %L for an SQL literal, and %I for an SQL identifier. The lattertwo are deliberately designed not to overlap with what sprintf() alreadysupports, in case we want to add more of sprintf()'s functionality herelater.Patch by Pavel Stehule, heavily revised by me. Reviewed by Jeff Janesand, in earlier versions, by Itagaki Takahiro and Tom Lane.
1 parent89a3684 commit7504870

File tree

7 files changed

+374
-15
lines changed

7 files changed

+374
-15
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 25 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1271,6 +1271,9 @@
12711271
<indexterm>
12721272
<primary>encode</primary>
12731273
</indexterm>
1274+
<indexterm>
1275+
<primary>format</primary>
1276+
</indexterm>
12741277
<indexterm>
12751278
<primary>initcap</primary>
12761279
</indexterm>
@@ -1496,6 +1499,28 @@
14961499
<entry><literal>MTIzAAE=</literal></entry>
14971500
</row>
14981501

1502+
<row>
1503+
<entry>
1504+
<literal><function>format</function>(<parameter>formatstr</parameter> <type>text</type>
1505+
[, <parameter>str</parameter> <type>"any"</type> [, ...] ])</literal>
1506+
</entry>
1507+
<entry><type>text</type></entry>
1508+
<entry>
1509+
Format a string. This function is similar to the C function
1510+
<function>sprintf</>; but only the following conversions
1511+
are recognized: <literal>%s</literal> interpolates the corresponding
1512+
argument as a string; <literal>%I</literal> escapes its argument as
1513+
an SQL identifier; <literal>%L</literal> escapes its argument as an
1514+
SQL literal; <literal>%%</literal> outputs a literal <literal>%</>.
1515+
A conversion can reference an explicit parameter position by preceding
1516+
the conversion specifier with <literal><replaceable>n</>$</>, where
1517+
<replaceable>n</replaceable> is the argument position.
1518+
See also <xref linkend="plpgsql-quote-literal-example">.
1519+
</entry>
1520+
<entry><literal>format('Hello %s, %1$s', 'World')</literal></entry>
1521+
<entry><literal>Hello World, World</literal></entry>
1522+
</row>
1523+
14991524
<row>
15001525
<entry><literal><function>initcap(<parameter>string</parameter>)</function></literal></entry>
15011526
<entry><type>text</type></entry>

‎doc/src/sgml/plpgsql.sgml

Lines changed: 23 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1152,6 +1152,11 @@ EXECUTE 'SELECT count(*) FROM '
11521152
<secondary>use in PL/pgSQL</secondary>
11531153
</indexterm>
11541154

1155+
<indexterm>
1156+
<primary>format</primary>
1157+
<secondary>use in PL/pgSQL</secondary>
1158+
</indexterm>
1159+
11551160
<para>
11561161
When working with dynamic commands you will often have to handle escaping
11571162
of single quotes. The recommended method for quoting fixed text in your
@@ -1250,6 +1255,24 @@ EXECUTE 'UPDATE tbl SET '
12501255
<emphasis>must</> use <function>quote_literal</>,
12511256
<function>quote_nullable</>, or <function>quote_ident</>, as appropriate.
12521257
</para>
1258+
1259+
<para>
1260+
Dynamic SQL statements can also be safely constructed using the
1261+
<function>format</function> function (see <xref
1262+
linkend="functions-string">). For example:
1263+
<programlisting>
1264+
EXECUTE format('UPDATE tbl SET %I = %L WHERE key = %L', colname, newvalue, keyvalue);
1265+
</programlisting>
1266+
The <function>format</function> function can be used in conjunction with
1267+
the <literal>USING</literal> clause:
1268+
<programlisting>
1269+
EXECUTE format('UPDATE tbl SET %I = $1 WHERE key = $2', colname)
1270+
USING newvalue, keyvalue;
1271+
</programlisting>
1272+
This form is more efficient, because the parameters
1273+
<literal>newvalue</literal> and <literal>keyvalue</literal> are not
1274+
converted to text.
1275+
</para>
12531276
</example>
12541277

12551278
<para>

‎src/backend/utils/adt/varlena.c

Lines changed: 195 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -15,6 +15,7 @@
1515
#include"postgres.h"
1616

1717
#include<ctype.h>
18+
#include<limits.h>
1819

1920
#include"access/tuptoaster.h"
2021
#include"catalog/pg_type.h"
@@ -74,6 +75,8 @@ static bytea *bytea_substring(Datum str,
7475
boollength_not_specified);
7576
staticbytea*bytea_overlay(bytea*t1,bytea*t2,intsp,intsl);
7677
staticStringInfomakeStringAggState(FunctionCallInfofcinfo);
78+
voidtext_format_string_conversion(StringInfobuf,charconversion,
79+
Oidtypid,Datumvalue,boolisNull);
7780

7881
staticDatumtext_to_array_internal(PG_FUNCTION_ARGS);
7982
statictext*array_to_text_internal(FunctionCallInfofcinfo,ArrayType*v,
@@ -3702,3 +3705,195 @@ text_reverse(PG_FUNCTION_ARGS)
37023705

37033706
PG_RETURN_TEXT_P(result);
37043707
}
3708+
3709+
/*
3710+
* Returns a formated string
3711+
*/
3712+
Datum
3713+
text_format(PG_FUNCTION_ARGS)
3714+
{
3715+
text*fmt;
3716+
StringInfoDatastr;
3717+
constchar*cp;
3718+
constchar*start_ptr;
3719+
constchar*end_ptr;
3720+
text*result;
3721+
intarg=0;
3722+
3723+
/* When format string is null, returns null */
3724+
if (PG_ARGISNULL(0))
3725+
PG_RETURN_NULL();
3726+
3727+
/* Setup for main loop. */
3728+
fmt=PG_GETARG_TEXT_PP(0);
3729+
start_ptr=VARDATA_ANY(fmt);
3730+
end_ptr=start_ptr+VARSIZE_ANY_EXHDR(fmt);
3731+
initStringInfo(&str);
3732+
3733+
/* Scan format string, looking for conversion specifiers. */
3734+
for (cp=start_ptr;cp<end_ptr;cp++)
3735+
{
3736+
Datumvalue;
3737+
boolisNull;
3738+
Oidtypid;
3739+
3740+
/*
3741+
* If it's not the start of a conversion specifier, just copy it to
3742+
* the output buffer.
3743+
*/
3744+
if (*cp!='%')
3745+
{
3746+
appendStringInfoCharMacro(&str,*cp);
3747+
continue;
3748+
}
3749+
3750+
/* Did we run off the end of the string? */
3751+
if (++cp >=end_ptr)
3752+
ereport(ERROR,
3753+
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
3754+
errmsg("unterminated conversion specifier")));
3755+
3756+
/* Easy case: %% outputs a single % */
3757+
if (*cp=='%')
3758+
{
3759+
appendStringInfoCharMacro(&str,*cp);
3760+
continue;
3761+
}
3762+
3763+
/*
3764+
* If the user hasn't specified an argument position, we just advance
3765+
* to the next one. If they have, we must parse it.
3766+
*/
3767+
if (*cp<'0'||*cp>'9')
3768+
++arg;
3769+
else
3770+
{
3771+
boolunterminated= false;
3772+
3773+
/* Parse digit string. */
3774+
arg=0;
3775+
do {
3776+
/* Treat overflowing arg position as unterminated. */
3777+
if (arg>INT_MAX /10)
3778+
break;
3779+
arg=arg*10+ (*cp-'0');
3780+
++cp;
3781+
}while (cp<end_ptr&&*cp >='0'&&*cp <='9');
3782+
3783+
/*
3784+
* If we ran off the end, or if there's not a $ next, or if the $
3785+
* is the last character, the conversion specifier is improperly
3786+
* terminated.
3787+
*/
3788+
if (cp==end_ptr||*cp!='$')
3789+
unterminated= true;
3790+
else
3791+
{
3792+
++cp;
3793+
if (cp==end_ptr)
3794+
unterminated= true;
3795+
}
3796+
if (unterminated)
3797+
ereport(ERROR,
3798+
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
3799+
errmsg("unterminated conversion specifier")));
3800+
3801+
/* There's no argument 0. */
3802+
if (arg==0)
3803+
ereport(ERROR,
3804+
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
3805+
errmsg("conversion specifies argument 0, but arguments are numbered from 1")));
3806+
}
3807+
3808+
/* Not enough arguments? Deduct 1 to avoid counting format string. */
3809+
if (arg>PG_NARGS()-1)
3810+
ereport(ERROR,
3811+
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
3812+
errmsg("too few arguments for format conversion")));
3813+
3814+
/*
3815+
* At this point, we should see the main conversion specifier.
3816+
* Whether or not an argument position was present, it's known
3817+
* that at least one character remains in the string at this point.
3818+
*/
3819+
value=PG_GETARG_DATUM(arg);
3820+
isNull=PG_ARGISNULL(arg);
3821+
typid=get_fn_expr_argtype(fcinfo->flinfo,arg);
3822+
3823+
switch (*cp)
3824+
{
3825+
case's':
3826+
case'I':
3827+
case'L':
3828+
text_format_string_conversion(&str,*cp,typid,value,isNull);
3829+
break;
3830+
default:
3831+
ereport(ERROR,
3832+
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
3833+
errmsg("unrecognized conversion specifier: %c",
3834+
*cp)));
3835+
}
3836+
}
3837+
3838+
/* Generate results. */
3839+
result=cstring_to_text_with_len(str.data,str.len);
3840+
pfree(str.data);
3841+
3842+
PG_RETURN_TEXT_P(result);
3843+
}
3844+
3845+
/* Format a %s, %I, or %L conversion. */
3846+
void
3847+
text_format_string_conversion(StringInfobuf,charconversion,
3848+
Oidtypid,Datumvalue,boolisNull)
3849+
{
3850+
OidtypOutput;
3851+
booltypIsVarlena;
3852+
char*str;
3853+
3854+
/* Handle NULL arguments before trying to stringify the value. */
3855+
if (isNull)
3856+
{
3857+
if (conversion=='L')
3858+
appendStringInfoString(buf,"NULL");
3859+
elseif (conversion=='I')
3860+
ereport(ERROR,
3861+
(errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
3862+
errmsg("NULL cannot be escaped as an SQL identifier")));
3863+
return;
3864+
}
3865+
3866+
/* Stringify. */
3867+
getTypeOutputInfo(typid,&typOutput,&typIsVarlena);
3868+
str=OidOutputFunctionCall(typOutput,value);
3869+
3870+
/* Escape. */
3871+
if (conversion=='I')
3872+
{
3873+
/* quote_identifier may or may not allocate a new string. */
3874+
appendStringInfoString(buf,quote_identifier(str));
3875+
}
3876+
elseif (conversion=='L')
3877+
{
3878+
char*qstr=quote_literal_cstr(str);
3879+
appendStringInfoString(buf,qstr);
3880+
/* quote_literal_cstr() always allocates a new string */
3881+
pfree(qstr);
3882+
}
3883+
else
3884+
appendStringInfoString(buf,str);
3885+
3886+
/* Cleanup. */
3887+
pfree(str);
3888+
}
3889+
3890+
/*
3891+
* text_format_nv - nonvariadic wrapper for text_format function.
3892+
*
3893+
* note: this wrapper is necessary to be sanity_checks test ok
3894+
*/
3895+
Datum
3896+
text_format_nv(PG_FUNCTION_ARGS)
3897+
{
3898+
returntext_format(fcinfo);
3899+
}

‎src/include/catalog/pg_proc.h

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2744,6 +2744,10 @@ DATA(insert OID = 3061 ( rightPGNSP PGUID 12 1 0 0 f f f t f i 2 0 25 "25 23"
27442744
DESCR("return the last n characters");
27452745
DATA(insertOID=3062 (reversePGNSPPGUID12100ffftfi1025"25"_null__null__null__null_text_reverse_null__null__null_ ));
27462746
DESCR("reverse text");
2747+
DATA(insertOID=3539 (formatPGNSPPGUID12102276fffffs2025"25 2276""{25,2276}""{i,v}"_null__null_text_format_null__null__null_ ));
2748+
DESCR("format text message");
2749+
DATA(insertOID=3540 (formatPGNSPPGUID12100fffffs1025"25"_null__null__null__null_text_format_nv_null__null__null_ ));
2750+
DESCR("format text message");
27472751

27482752
DATA(insertOID=1810 (bit_lengthPGNSPPGUID14100ffftfi1023"17"_null__null__null__null_"select pg_catalog.octet_length($1) * 8"_null__null__null_ ));
27492753
DESCR("length in bits");

‎src/include/utils/builtins.h

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -743,6 +743,8 @@ extern Datum text_concat_ws(PG_FUNCTION_ARGS);
743743
externDatumtext_left(PG_FUNCTION_ARGS);
744744
externDatumtext_right(PG_FUNCTION_ARGS);
745745
externDatumtext_reverse(PG_FUNCTION_ARGS);
746+
externDatumtext_format(PG_FUNCTION_ARGS);
747+
externDatumtext_format_nv(PG_FUNCTION_ARGS);
746748

747749
/* version.c */
748750
externDatumpgsql_version(PG_FUNCTION_ARGS);

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp