<!-- $PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.149 2009/12/28 19:11:51 petere Exp $ -->

<!-- $PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.150 2010/01/19 01:35:30 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> <optional> USING <replaceable>expression</replaceable> <optional>, ...</optional> </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

<synopsis>

RETURN NEXT <replaceable>expression</replaceable>;

RETURN QUERY <replaceable>query</replaceable>;

RETURN QUERY EXECUTE <replaceable class="command">command-string</replaceable> <optional> USING <replaceable>expression</replaceable> <optional>, ...</optional> </optional>;

RETURN QUERY EXECUTE <replaceable class="command">command-string</replaceable> <optional> USING <replaceable>expression</replaceable> <optional>, ...</optional> </optional>;

</synopsis>

<para>

rows:

<synopsis>

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

FOR <replaceable>target</replaceable> IN EXECUTE <replaceable>text_expression</replaceable> <optional> USING <replaceable>expression</replaceable> <optional>, ...</optional> </optional> 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>

<title><command>OPEN FOR EXECUTE</command></title>

<synopsis>

OPEN <replaceable>unbound_cursorvar</replaceable> <optional> <optional> NO </optional> SCROLL </optional> FOR EXECUTE <replaceable class="command">query_string</replaceable>;

OPEN <replaceable>unbound_cursorvar</replaceable> <optional> <optional> NO </optional> SCROLL </optional> FOR EXECUTE <replaceable class="command">query_string</replaceable> <optional> USING <replaceable>expression</replaceable> <optional>, ... </optional> </optional>;

</synopsis>

<para>

command. As usual, this gives flexibility so the query plan can vary

from one run to the next (see <xref linkend="plpgsql-plan-caching">),

and it also means that variable substitution is not done on the

command string.

command string. As with <command>EXECUTE</command>, parameter values

can be inserted into the dynamic command via <literal>USING</>.

The <literal>SCROLL</> and

<literal>NO SCROLL</> options have the same meanings as for a bound

cursor.

<para>

An example:

<programlisting>

OPEN curs1 FOR EXECUTE 'SELECT * FROM ' || quote_ident($1);

OPEN curs1 FOR EXECUTE 'SELECT * FROM ' || quote_ident(tabname) ' WHERE col1 = $1' USING keyvalue;

</programlisting>

In this example, the table name is inserted into the query textually,

so use of <function>quote_ident()</> is recommended to guard against

SQL injection. The comparison value for <literal>col1</> is inserted

via a <literal>USING</> parameter, so it needs no quoting.

</para>

</sect3>

raise errors.

<synopsis>

RAISE <optional> <replaceable class="parameter">level</replaceable> </optional> '<replaceable class="parameter">format</replaceable>' <optional>, <replaceable class="parameter">expression</replaceable> <optional>, ...</optional></optional> <optional> USING <replaceable class="parameter">option</replaceable> = <replaceable class="parameter">expression</replaceable> <optional>, ... </optional> </optional>;

RAISE <optional> <replaceable class="parameter">level</replaceable> </optional> '<replaceable class="parameter">format</replaceable>' <optional>, <replaceable class="parameter">expression</replaceable> <optional>, ...</optional></optional> <optional> USING <replaceable class="parameter">option</replaceable> = <replaceable class="parameter">expression</replaceable> <optional>, ... </optional> </optional>;

RAISE <optional> <replaceable class="parameter">level</replaceable> </optional> <replaceable class="parameter">condition_name</> <optional> USING <replaceable class="parameter">option</replaceable> = <replaceable class="parameter">expression</replaceable> <optional>, ... </optional> </optional>;

RAISE <optional> <replaceable class="parameter">level</replaceable> </optional> SQLSTATE '<replaceable class="parameter">sqlstate</>' <optional> USING <replaceable class="parameter">option</replaceable> = <replaceable class="parameter">expression</replaceable> <optional>, ... </optional> </optional>;

RAISE <optional> <replaceable class="parameter">level</replaceable> </optional> USING <replaceable class="parameter">option</replaceable> = <replaceable class="parameter">expression</replaceable> <optional>, ... </optional>;

tok =yylex();

if (tok == K_EXECUTE)

{

new->dynquery =read_sql_stmt("SELECT");

intendtoken;

new->dynquery =

read_sql_expression2(K_USING,';',

"USING or ;",

&endtoken);

if (endtoken == K_USING)

{

PLpgSQL_expr *expr;

{

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

", or ;",

&endtoken);

new->params =lappend(new->params,

expr);

}while (endtoken ==',');

}

}

{

List*params);

staticvoidfree_params_data(PreparedParamsData*ppd);

staticPortalexec_dynquery_with_params(PLpgSQL_execstate*estate,

PLpgSQL_expr*query,List*params);

PLpgSQL_expr*dynquery,List*params,

constchar*portalname,intcursorOptions);

Assert(stmt->dynquery!=NULL);

portal=exec_dynquery_with_params(estate,stmt->dynquery,

stmt->params);

stmt->params,NULL,0);

}

tupmap=convert_tuples_by_position(portal->tupDesc,

Portalportal;

intrc;

portal=exec_dynquery_with_params(estate,stmt->query,stmt->params);

portal=exec_dynquery_with_params(estate,stmt->query,stmt->params,

NULL,0);

PLpgSQL_expr*query;

Portalportal;

ParamListInfoparamLI;

boolisnull;

DatumqueryD;

Oidrestype;

char*querystr;

SPIPlanPtrcurplan;

queryD=exec_eval_expr(estate,stmt->dynquery,&isnull,&restype);

if (isnull)

ereport(ERROR,

(errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),

errmsg("query string argument of EXECUTE is null")));

querystr=convert_value_to_string(queryD,restype);

exec_eval_cleanup(estate);

curplan=SPI_prepare_cursor(querystr,0,NULL,stmt->cursor_options);

if (curplan==NULL)

elog(ERROR,"SPI_prepare_cursor failed for \"%s\": %s",

querystr,SPI_result_code_string(SPI_result));

portal=SPI_cursor_open(curname,curplan,NULL,NULL,

estate->readonly_func);

if (portal==NULL)

elog(ERROR,"could not open cursor for query \"%s\": %s",

querystr,SPI_result_code_string(SPI_result));

pfree(querystr);

SPI_freeplan(curplan);

portal=exec_dynquery_with_params(estate,

stmt->dynquery,

stmt->params,

curname,

stmt->cursor_options);

exec_dynquery_with_params(PLpgSQL_execstate*estate,PLpgSQL_expr*dynquery,

List*params)

exec_dynquery_with_params(PLpgSQL_execstate*estate,

PLpgSQL_expr*dynquery,

List*params,

constchar*portalname,

intcursorOptions)

{

Portalportal;

Datumquery;

PreparedParamsData*ppd;

ppd=exec_eval_using_params(estate,params);

portal=SPI_cursor_open_with_args(NULL,

portal=SPI_cursor_open_with_args(portalname,

querystr,

ppd->nargs,ppd->types,

ppd->values,ppd->nulls,

estate->readonly_func,0);

estate->readonly_func,

cursorOptions);

free_params_data(ppd);

}

{

portal=SPI_cursor_open_with_args(NULL,

portal=SPI_cursor_open_with_args(portalname,

querystr,

0,NULL,

NULL,NULL,

estate->readonly_func,0);

estate->readonly_func,

cursorOptions);

}

if (portal==NULL)

printf(" execute = '");

dump_expr(stmt->dynquery);

printf("'\n");

if (stmt->params!=NIL)

{

ListCell*lc;

inti;

dump_indent+=2;

dump_ind();

printf(" USING\n");

dump_indent+=2;

i=1;

foreach(lc,stmt->params)

{

dump_ind();

printf(" parameter $%d: ",i++);

dump_expr((PLpgSQL_expr*)lfirst(lc));

printf("\n");

}

dump_indent-=4;

}

}

dump_indent-=2;

}

PLpgSQL_expr*argquery;

PLpgSQL_expr*query;

PLpgSQL_expr*dynquery;

List*params;/* USING expressions */

}PLpgSQL_stmt_open;

26

(1 row)

drop function exc_using(int, text);

create or replace function exc_using(int) returns void as $$

declare

c refcursor;

i int;

begin

open c for execute 'select * from generate_series(1,$1)' using $1+1;

loop

fetch c into i;

exit when not found;

raise notice '%', i;

end loop;

close c;

return;

end;

$$ language plpgsql;

select exc_using(5);

NOTICE: 1

NOTICE: 2

NOTICE: 3

NOTICE: 4

NOTICE: 5

NOTICE: 6

exc_using

-----------

(1 row)

drop function exc_using(int);

-- test FOR-over-cursor

create or replace function forc01() returns void as $$

declare

select exc_using(5,'foobar');

dropfunction exc_using(int,text);

create or replacefunctionexc_using(int) returns voidas $$

declare

c refcursor;

iint;

open c for execute'select * from generate_series(1,$1)' using $1+1;

loop

fetch c into i;

exit when not found;

raise notice'%', i;

end loop;

close c;

return;

end;

$$ language plpgsql;

select exc_using(5);

dropfunction exc_using(int);

create or replacefunctionforc01() returns voidas $$