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

Commit9aac623

Browse files
committed
Add item for plpgsql temp table access.
1 parent090319b commit9aac623

File tree

2 files changed

+40
-16
lines changed

2 files changed

+40
-16
lines changed

‎doc/FAQ

Lines changed: 14 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,7 +1,7 @@
11

22
Frequently Asked Questions (FAQ) for PostgreSQL
33

4-
Last updated:Fri Apr 26 23:03:46 EDT 2002
4+
Last updated:Mon Jun 10 15:47:38 EDT 2002
55

66
Current maintainer: Bruce Momjian (pgman@candle.pha.pa.us)
77

@@ -92,6 +92,8 @@
9292
4.23) How do I perform an outer join?
9393
4.24) How do I perform queries using multiple databases?
9494
4.25) How do I return multiple rows or columns from a function?
95+
4.26) Why can't I reliably create/drop temporary tables in PL/PgSQL
96+
functions?
9597

9698
Extending PostgreSQL
9799

@@ -1031,6 +1033,17 @@ SELECT *
10311033
http://developer.postgresql.org/docs/postgres/plpgsql-cursors.html,
10321034
section 23.7.3.3.
10331035

1036+
4.26) Why can't I reliably create/drop temporary tables in PL/PgSQL
1037+
functions?
1038+
1039+
PL/PgSQL caches function contents, and an unfortunate side effect is
1040+
that if a PL/PgSQL function accesses a temporary table, and that table
1041+
is later dropped and recreated, and the function called again, the
1042+
function will fail because the cached function contents still point to
1043+
the old temporary table. The solution is to use EXECUTE for temporary
1044+
table access in PL/PgSQL. This will cause the query to be reparsed
1045+
every time.
1046+
10341047
Extending PostgreSQL
10351048

10361049
5.1) I wrote a user-defined function. When I run it in psql, why does it

‎doc/src/FAQ/FAQ.html

Lines changed: 26 additions & 15 deletions
Original file line numberDiff line numberDiff line change
@@ -14,7 +14,7 @@
1414
alink="#0000ff">
1515
<H1>Frequently Asked Questions (FAQ) for PostgreSQL</H1>
1616

17-
<P>Last updated:Fri Apr 26 23:03:46 EDT 2002</P>
17+
<P>Last updated:Mon Jun 10 15:47:38 EDT 2002</P>
1818

1919
<P>Current maintainer: Bruce Momjian (<Ahref=
2020
"mailto:pgman@candle.pha.pa.us">pgman@candle.pha.pa.us</A>)<BR>
@@ -138,7 +138,9 @@ <H2 align="center">Operational Questions</H2>
138138
<Ahref="#4.24">4.24</A>) How do I perform queries using multiple
139139
databases?<BR>
140140
<Ahref="#4.25">4.25</A>) How do I return multiple rows or columns
141-
from a function?<BR>
141+
from a function?<BR>
142+
<Ahref="#4.26">4.26</A>) Why can't I reliably create/drop
143+
temporary tables in PL/PgSQL functions?<BR>
142144

143145

144146
<H2align="center">Extending PostgreSQL</H2>
@@ -742,7 +744,7 @@ <H4><A name="3.7">3.7</A>) What debugging features are
742744
<P>You can also compile with profiling to see what functions are
743745
taking execution time. The backend profile files will be deposited
744746
in the<I>pgsql/data/base/dbname</I> directory. The client profile
745-
file will be put in the client's current directory.Linux requires
747+
file will be put in the client's current directory. Linux requires
746748
a compile with<I>-DLINUX_PROFILE</I> for proper profiling.</P>
747749

748750
<H4><Aname="3.8">3.8</A>) Why do I get<I>"Sorry, too many
@@ -900,9 +902,9 @@ <H4><A name="4.7">4.7</A>) How do I find out what tables, indexes,
900902
databases, and users are defined?</H4>
901903

902904
<P><I>psql</I> has a variety of backslash commands to show such
903-
information. Use \? to see them.There are also system tables
904-
beginning with<i>pg_</i> that describe these too.Also,<i>psql
905-
-l</i> will list all databases.</P>
905+
information. Use \? to see them. There are also system tables
906+
beginning with<I>pg_</I> that describe these too. Also,<I>psql
907+
-l</I> will list all databases.</P>
906908

907909
<P>Also try the file<I>pgsql/src/tutorial/syscat.source</I>. It
908910
illustrates many of the<SMALL>SELECT</SMALL>s needed to get
@@ -1307,16 +1309,25 @@ <H4><A name="4.24">4.24</A>) How do I perform queries using
13071309
different databases and merge the information that way.</P>
13081310
<HR>
13091311

1310-
<H4><Aname="4.25">4.25</A>) How do I return multiple rows or columns
1311-
from a function?</H4>
1312+
<H4><Aname="4.25">4.25</A>) How do I return multiple rows or
1313+
columns from a function?</H4>
1314+
1315+
<P>You can return result sets from PL/pgSQL functions using
1316+
<I>refcursors</I>. See<Ahref=
1317+
"http://developer.postgresql.org/docs/postgres/plpgsql-cursors.html">
1318+
http://developer.postgresql.org/docs/postgres/plpgsql-cursors.html,</A>
1319+
section 23.7.3.3.</P>
1320+
1321+
<H4><Ahref="#4.26">4.26</A>) Why can't I reliably create/drop
1322+
temporary tables in PL/PgSQL functions?</H4>
1323+
PL/PgSQL caches function contents, and an unfortunate side effect
1324+
is that if a PL/PgSQL function accesses a temporary table, and that
1325+
table is later dropped and recreated, and the function called
1326+
again, the function will fail because the cached function contents
1327+
still point to the old temporary table. The solution is to use
1328+
<SMALL>EXECUTE</SMALL> for temporary table access in PL/PgSQL. This
1329+
will cause the query to be reparsed every time.
13121330

1313-
<P>You can return result sets from PL/pgSQL functions using
1314-
<i>refcursors</i>. See<a
1315-
href="http://developer.postgresql.org/docs/postgres/plpgsql-cursors.html">
1316-
http://developer.postgresql.org/docs/postgres/plpgsql-cursors.html,</a>
1317-
section 23.7.3.3.</P>
1318-
1319-
13201331
<H2align="center">Extending PostgreSQL</H2>
13211332

13221333
<H4><Aname="5.1">5.1</A>) I wrote a user-defined function. When I

0 commit comments

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