<!-- $PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.125 2008/03/28 00:21:55 tgl Exp $ -->

<!-- $PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.126 2008/04/01 03:51:09 tgl Exp $ -->

<chapter id="plpgsql">

<title><application>PL/pgSQL</application> - <acronym>SQL</acronym> Procedural Language</title>

<command>EXECUTE</command> statement is provided:

<synopsis>

EXECUTE <replaceable class="command">command-string</replaceable> <optional> INTO <optional>STRICT</optional> <replaceable>target</replaceable> </optional>;

EXECUTE <replaceable class="command">command-string</replaceable> <optional> INTO <optional>STRICT</optional> <replaceable>target</replaceable> </optional> <optional> USING <replaceable>expression</replaceable> <optional>, ...</optional> </optional>;

</synopsis>

where <replaceable>command-string</replaceable> is an expression

yielding a string (of type <type>text</type>) containing the

command to be executed and <replaceable>target</replaceable> is a

record variable, row variable, or a comma-separated list of

simple variables and record/row fields.

command to be executed. The optional <replaceable>target</replaceable>

is a record variable, a row variable, or a comma-separated list of

simple variables and record/row fields, into which the results of

the command will be stored. The optional <literal>USING</> expressions

supply values to be inserted into the command.

</para>

<para>

No substitution of <application>PL/pgSQL</> variables is done on the

computed command string. Any required variable values must be inserted

in the command string as it is constructed.

in the command string as it is constructed; or you can use parameters

as described below.

</para>

<para>

If the <literal>STRICT</> option is given, an error is reported

unless the query produces exactly one row.

</para>

<para>

The command string can use parameter values, which are referenced

in the command as <literal>$1</>, <literal>$2</>, etc.

These symbols refer to values supplied in the <literal>USING</>

clause. This method is often preferable to inserting data values

into the command string as text: it avoids run-time overhead of

converting the values to text and back, and it is much less prone

to SQL-injection attacks since there is no need for quoting or escaping.

An example is:

<programlisting>

EXECUTE 'SELECT count(*) FROM mytable WHERE inserted_by = $1 AND inserted <= $2'

INTO c

USING checked_user, checked_date;

</programlisting>

Note that parameter symbols can only be used for data values

&mdash; if you want to use dynamically determined table or column

names, you must insert them into the command string textually.

For example, if the preceding query needed to be done against a

dynamically selected table, you could do this:

<programlisting>

EXECUTE 'SELECT count(*) FROM '

|| tabname::regclass

|| ' WHERE inserted_by = $1 AND inserted <= $2'

INTO c

USING checked_user, checked_date;

</programlisting>

</para>

<para>

An <command>EXECUTE</> with a simple constant command string and some

<literal>USING</> parameters, as in the first example above, is

functionally equivalent to just writing the command directly in

<application>PL/pgSQL</application> and allowing replacement of

<application>PL/pgSQL</application> variables to happen automatically.

The important difference is that <command>EXECUTE</> will re-plan

the command on each execution, generating a plan that is specific

to the current parameter values; whereas

<application>PL/pgSQL</application> normally creates a generic plan

and caches it for re-use. In situations where the best plan depends

strongly on the parameter values, <command>EXECUTE</> can be

significantly faster; while when the plan is not sensitive to parameter

values, re-planning will be a waste.

</para>

<para>

<command>SELECT INTO</command> is not currently supported within

rows:

<synopsis>

<optional> &lt;&lt;<replaceable>label</replaceable>&gt;&gt; </optional>

FOR <replaceable>target</replaceable> IN EXECUTE <replaceable>text_expression</replaceable> LOOP

FOR <replaceable>target</replaceable> IN EXECUTE <replaceable>text_expression</replaceable><optional> USING <replaceable>expression</replaceable> <optional>, ...</optional> </optional>LOOP

<replaceable>statements</replaceable>

END LOOP <optional> <replaceable>label</replaceable> </optional>;

</synopsis>

on each entry to the <literal>FOR</> loop. This allows the programmer to

choose the speed of a preplanned query or the flexibility of a dynamic

query, just as with a plain <command>EXECUTE</command> statement.

As with <command>EXECUTE</command>, parameter values can be inserted

into the dynamic command via <literal>USING</>.

</para>

</sect2>

static PLpgSQL_expr*read_sql_construct(int until,

int until2,

int until3,

constchar *expected,

constchar *sqlstart,

bool isexpression,

bool valid_sql,

int *endtoken);

static PLpgSQL_expr*read_sql_expression2(int until,int until2,

constchar *expected,

int *endtoken);

staticPLpgSQL_expr*read_sql_stmt(constchar *sqlstart);

staticPLpgSQL_type*read_datatype(int tok);

staticPLpgSQL_stmt*make_execsql_stmt(constchar *sqlstart,int lineno);

{

PLpgSQL_stmt_dynfors*new;

PLpgSQL_expr*expr;

intterm;

expr = plpgsql_read_expression(K_LOOP,"LOOP");

expr = read_sql_expression2(K_LOOP, K_USING,

"LOOP or USING",

&term);

new = palloc0(sizeof(PLpgSQL_stmt_dynfors));

new->cmd_type = PLPGSQL_STMT_DYNFORS;

}

new->query = expr;

if (term == K_USING)

{

{

expr = read_sql_expression2(',', K_LOOP,

", or LOOP",

&term);

new->params = lappend(new->params, expr);

}while (term ==',');

}

$$ = (PLpgSQL_stmt *)new;

}

expr1 = read_sql_construct(K_DOTDOT,

K_LOOP,

0,

"LOOP",

"SELECT",

true,

check_sql_expr(expr1->query);

expr2 = read_sql_construct(K_LOOP,

K_BY,

"LOOP",

"SELECT",

true,

true,

&tok);

expr2 = read_sql_expression2(K_LOOP, K_BY,

"LOOP",

&tok);

if (tok == K_BY)

expr_by = plpgsql_read_expression(K_LOOP,"LOOP");

expr_by = plpgsql_read_expression(K_LOOP,

"LOOP");

expr_by =NULL;

if (tok ==',')

{

PLpgSQL_expr *expr;

int term;

for (;;)

{

expr = read_sql_construct(',',';',", or ;",

"SELECT",

true,true, &term);

PLpgSQL_expr *expr;

expr = read_sql_expression2(',',';',

", or ;",

&tok);

new->params = lappend(new->params, expr);

if (term ==';')

break;

}

}while (tok ==',');

}

$$ = (PLpgSQL_stmt *)new;

PLpgSQL_expr *expr;

int endtoken;

expr = read_sql_construct(K_INTO,';',"INTO|;",

expr = read_sql_construct(K_INTO, K_USING,';',

"INTO or USING or ;",

"SELECT",

true,true, &endtoken);

new->strict =false;

new->rec =NULL;

new->row =NULL;

new->params = NIL;

if (endtoken == K_INTO)

{

new->into =true;

read_into_target(&new->rec, &new->row, &new->strict);

if (yylex() !=';')

endtoken =yylex();

if (endtoken !=';' && endtoken != K_USING)

yyerror("syntax error");

}

if (endtoken == K_USING)

{

{

expr = read_sql_expression2(',',';',

", or ;",

&endtoken);

new->params = lappend(new->params, expr);

}while (endtoken ==',');

}

$$ = (PLpgSQL_stmt *)new;

}

;

$$ = (PLpgSQL_stmt *)fetch;

}

;

stmt_move:K_MOVElnoopt_fetch_directioncursor_variable';'

{

PLpgSQL_stmt_fetch *fetch =$3;

}

PLpgSQL_expr *

plpgsql_read_expression(int until,constchar *expected)

{

returnread_sql_construct(until,0, expected,"SELECT",true,true,NULL);

returnread_sql_construct(until,0,0, expected,

"SELECT",true,true,NULL);

}

static PLpgSQL_expr *

read_sql_expression2(int until,int until2,constchar *expected,

int *endtoken)

{

returnread_sql_construct(until, until2,0, expected,

"SELECT",true,true, endtoken);

}

static PLpgSQL_expr *

read_sql_stmt(constchar *sqlstart)

{

returnread_sql_construct(';',0,";", sqlstart,false,true,NULL);

returnread_sql_construct(';',0,0,";",

sqlstart,false,true,NULL);

}

static PLpgSQL_expr *

read_sql_construct(int until,

int until2,

int until3,

constchar *expected,

constchar *sqlstart,

bool isexpression,

break;

if (tok == until2 && parenlevel ==0)

break;

if (tok == until3 && parenlevel ==0)

break;

if (tok =='(' || tok =='[')

parenlevel++;

elseif (tok ==')' || tok ==']')

elseif (pg_strcasecmp(yytext,"absolute") ==0)

{

fetch->direction = FETCH_ABSOLUTE;

fetch->expr =read_sql_construct(K_FROM, K_IN,"FROM or IN",

"SELECT",true,true,NULL);

fetch->expr =read_sql_expression2(K_FROM, K_IN,

"FROM or IN",

NULL);

check_FROM =false;

}

elseif (pg_strcasecmp(yytext,"relative") ==0)

{

fetch->direction = FETCH_RELATIVE;

fetch->expr =read_sql_construct(K_FROM, K_IN,"FROM or IN",

"SELECT",true,true,NULL);

fetch->expr =read_sql_expression2(K_FROM, K_IN,

"FROM or IN",

NULL);

check_FROM =false;

}

elseif (pg_strcasecmp(yytext,"forward") ==0)

elseif (tok != T_SCALAR)

{

plpgsql_push_back_token(tok);

fetch->expr =read_sql_construct(K_FROM, K_IN,"FROM or IN",

"SELECT",true,true,NULL);

fetch->expr =read_sql_expression2(K_FROM, K_IN,

"FROM or IN",

NULL);

check_FROM =false;

}

new =palloc0(sizeof(PLpgSQL_stmt_return_query));

new->cmd_type = PLPGSQL_STMT_RETURN_QUERY;

new->lineno = lineno;

new->query =read_sql_construct(';',0,")","",false,true,NULL);

new->query =read_sql_stmt("");

return (PLpgSQL_stmt *)new;

}