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

Commit11d3371

Browse files
committed
Fix inappropriate quoting in dblink. From Joe Conway.
1 parentc363720 commit11d3371

File tree

4 files changed

+116
-12
lines changed

4 files changed

+116
-12
lines changed

‎contrib/dblink/README.dblink‎

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -151,6 +151,10 @@ Installation:
151151

152152
Documentation:
153153

154+
Note: Parameters representing relation names must include double
155+
quotes if the names are mixed-case or contain special characters. They
156+
must also be appropriately qualified with schema name if applicable.
157+
154158
See the following files:
155159
doc/connection
156160
doc/cursor

‎contrib/dblink/dblink.c‎

Lines changed: 55 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -71,6 +71,7 @@ static dblink_results *get_res_ptr(int32 res_id_index);
7171
staticvoidappend_res_ptr(dblink_results*results);
7272
staticvoidremove_res_ptr(dblink_results*results);
7373
staticTupleDescpgresultGetTupleDesc(PGresult*res);
74+
staticchar*generate_relation_name(Oidrelid);
7475

7576
/* Global */
7677
List*res_id=NIL;
@@ -171,7 +172,7 @@ dblink_open(PG_FUNCTION_ARGS)
171172
}
172173
PQclear(res);
173174

174-
appendStringInfo(str,"DECLARE %s CURSOR FOR %s",quote_ident_cstr(curname),sql);
175+
appendStringInfo(str,"DECLARE %s CURSOR FOR %s",curname,sql);
175176
res=PQexec(conn,str->data);
176177
if (!res||
177178
(PQresultStatus(res)!=PGRES_COMMAND_OK&&
@@ -210,7 +211,7 @@ dblink_close(PG_FUNCTION_ARGS)
210211
else
211212
elog(ERROR,"dblink_close: no connection available");
212213

213-
appendStringInfo(str,"CLOSE %s",quote_ident_cstr(curname));
214+
appendStringInfo(str,"CLOSE %s",curname);
214215

215216
/* close the cursor */
216217
res=PQexec(conn,str->data);
@@ -287,7 +288,7 @@ dblink_fetch(PG_FUNCTION_ARGS)
287288
else
288289
elog(ERROR,"dblink_fetch: no connection available");
289290

290-
appendStringInfo(str,"FETCH %d FROM %s",howmany,quote_ident_cstr(curname));
291+
appendStringInfo(str,"FETCH %d FROM %s",howmany,curname);
291292

292293
res=PQexec(conn,str->data);
293294
if (!res||
@@ -306,7 +307,7 @@ dblink_fetch(PG_FUNCTION_ARGS)
306307
{
307308
/* cursor does not exist - closed already or bad name */
308309
PQclear(res);
309-
elog(ERROR,"dblink_fetch: cursor %s does not exist",quote_ident_cstr(curname));
310+
elog(ERROR,"dblink_fetch: cursor %s does not exist",curname);
310311
}
311312

312313
funcctx->max_calls=PQntuples(res);
@@ -1527,19 +1528,21 @@ get_sql_insert(Oid relid, int16 *pkattnums, int16 pknumatts, char **src_pkattval
15271528
inti;
15281529
boolneedComma;
15291530

1531+
/* get relation name including any needed schema prefix and quoting */
1532+
relname=generate_relation_name(relid);
1533+
15301534
/*
15311535
* Open relation using relid
15321536
*/
15331537
rel=relation_open(relid,AccessShareLock);
1534-
relname=RelationGetRelationName(rel);
15351538
tupdesc=rel->rd_att;
15361539
natts=tupdesc->natts;
15371540

15381541
tuple=get_tuple_of_interest(relid,pkattnums,pknumatts,src_pkattvals);
15391542
if (!tuple)
15401543
elog(ERROR,"dblink_build_sql_insert: row not found");
15411544

1542-
appendStringInfo(str,"INSERT INTO %s(",quote_ident_cstr(relname));
1545+
appendStringInfo(str,"INSERT INTO %s(",relname);
15431546

15441547
needComma= false;
15451548
for (i=0;i<natts;i++)
@@ -1610,15 +1613,17 @@ get_sql_delete(Oid relid, int16 *pkattnums, int16 pknumatts, char **tgt_pkattval
16101613
char*val;
16111614
inti;
16121615

1616+
/* get relation name including any needed schema prefix and quoting */
1617+
relname=generate_relation_name(relid);
1618+
16131619
/*
16141620
* Open relation using relid
16151621
*/
16161622
rel=relation_open(relid,AccessShareLock);
1617-
relname=RelationGetRelationName(rel);
16181623
tupdesc=rel->rd_att;
16191624
natts=tupdesc->natts;
16201625

1621-
appendStringInfo(str,"DELETE FROM %s WHERE ",quote_ident_cstr(relname));
1626+
appendStringInfo(str,"DELETE FROM %s WHERE ",relname);
16221627
for (i=0;i<pknumatts;i++)
16231628
{
16241629
int16pkattnum=pkattnums[i];
@@ -1669,19 +1674,21 @@ get_sql_update(Oid relid, int16 *pkattnums, int16 pknumatts, char **src_pkattval
16691674
inti;
16701675
boolneedComma;
16711676

1677+
/* get relation name including any needed schema prefix and quoting */
1678+
relname=generate_relation_name(relid);
1679+
16721680
/*
16731681
* Open relation using relid
16741682
*/
16751683
rel=relation_open(relid,AccessShareLock);
1676-
relname=RelationGetRelationName(rel);
16771684
tupdesc=rel->rd_att;
16781685
natts=tupdesc->natts;
16791686

16801687
tuple=get_tuple_of_interest(relid,pkattnums,pknumatts,src_pkattvals);
16811688
if (!tuple)
16821689
elog(ERROR,"dblink_build_sql_update: row not found");
16831690

1684-
appendStringInfo(str,"UPDATE %s SET ",quote_ident_cstr(relname));
1691+
appendStringInfo(str,"UPDATE %s SET ",relname);
16851692

16861693
needComma= false;
16871694
for (i=0;i<natts;i++)
@@ -1813,11 +1820,13 @@ get_tuple_of_interest(Oid relid, int16 *pkattnums, int16 pknumatts, char **src_p
18131820
inti;
18141821
char*val=NULL;
18151822

1823+
/* get relation name including any needed schema prefix and quoting */
1824+
relname=generate_relation_name(relid);
1825+
18161826
/*
18171827
* Open relation using relid
18181828
*/
18191829
rel=relation_open(relid,AccessShareLock);
1820-
relname=RelationGetRelationName(rel);
18211830
tupdesc=CreateTupleDescCopy(rel->rd_att);
18221831
relation_close(rel,AccessShareLock);
18231832

@@ -1831,7 +1840,7 @@ get_tuple_of_interest(Oid relid, int16 *pkattnums, int16 pknumatts, char **src_p
18311840
* Build sql statement to look up tuple of interest Use src_pkattvals
18321841
* as the criteria.
18331842
*/
1834-
appendStringInfo(str,"SELECT * FROM %s WHERE ",quote_ident_cstr(relname));
1843+
appendStringInfo(str,"SELECT * FROM %s WHERE ",relname);
18351844

18361845
for (i=0;i<pknumatts;i++)
18371846
{
@@ -2003,3 +2012,37 @@ pgresultGetTupleDesc(PGresult *res)
20032012

20042013
returndesc;
20052014
}
2015+
2016+
/*
2017+
* generate_relation_name - copied from ruleutils.c
2018+
*Compute the name to display for a relation specified by OID
2019+
*
2020+
* The result includes all necessary quoting and schema-prefixing.
2021+
*/
2022+
staticchar*
2023+
generate_relation_name(Oidrelid)
2024+
{
2025+
HeapTupletp;
2026+
Form_pg_classreltup;
2027+
char*nspname;
2028+
char*result;
2029+
2030+
tp=SearchSysCache(RELOID,
2031+
ObjectIdGetDatum(relid),
2032+
0,0,0);
2033+
if (!HeapTupleIsValid(tp))
2034+
elog(ERROR,"cache lookup of relation %u failed",relid);
2035+
reltup= (Form_pg_class)GETSTRUCT(tp);
2036+
2037+
/* Qualify the name if not visible in search path */
2038+
if (RelationIsVisible(relid))
2039+
nspname=NULL;
2040+
else
2041+
nspname=get_namespace_name(reltup->relnamespace);
2042+
2043+
result=quote_qualified_identifier(nspname,NameStr(reltup->relname));
2044+
2045+
ReleaseSysCache(tp);
2046+
2047+
returnresult;
2048+
}

‎contrib/dblink/expected/dblink.out‎

Lines changed: 37 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -59,6 +59,43 @@ SELECT dblink_build_sql_delete('foo','1 2',2,'{"0", "a"}');
5959
DELETE FROM foo WHERE f1 = '0' AND f2 = 'a'
6060
(1 row)
6161

62+
-- retest using a quoted and schema qualified table
63+
CREATE SCHEMA "MySchema";
64+
CREATE TABLE "MySchema"."Foo"(f1 int, f2 text, f3 text[], primary key (f1,f2));
65+
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'Foo_pkey' for table 'Foo'
66+
INSERT INTO "MySchema"."Foo" VALUES (0,'a','{"a0","b0","c0"}');
67+
-- list the primary key fields
68+
SELECT *
69+
FROM dblink_get_pkey('"MySchema"."Foo"');
70+
position | colname
71+
----------+---------
72+
1 | f1
73+
2 | f2
74+
(2 rows)
75+
76+
-- build an insert statement based on a local tuple,
77+
-- replacing the primary key values with new ones
78+
SELECT dblink_build_sql_insert('"MySchema"."Foo"','1 2',2,'{"0", "a"}','{"99", "xyz"}');
79+
dblink_build_sql_insert
80+
------------------------------------------------------------------------
81+
INSERT INTO "MySchema"."Foo"(f1,f2,f3) VALUES('99','xyz','{a0,b0,c0}')
82+
(1 row)
83+
84+
-- build an update statement based on a local tuple,
85+
-- replacing the primary key values with new ones
86+
SELECT dblink_build_sql_update('"MySchema"."Foo"','1 2',2,'{"0", "a"}','{"99", "xyz"}');
87+
dblink_build_sql_update
88+
-----------------------------------------------------------------------------------------------------
89+
UPDATE "MySchema"."Foo" SET f1 = '99', f2 = 'xyz', f3 = '{a0,b0,c0}' WHERE f1 = '99' AND f2 = 'xyz'
90+
(1 row)
91+
92+
-- build a delete statement based on a local tuple,
93+
SELECT dblink_build_sql_delete('"MySchema"."Foo"','1 2',2,'{"0", "a"}');
94+
dblink_build_sql_delete
95+
----------------------------------------------------------
96+
DELETE FROM "MySchema"."Foo" WHERE f1 = '0' AND f2 = 'a'
97+
(1 row)
98+
6299
-- regular old dblink
63100
SELECT *
64101
FROM dblink('dbname=regression','SELECT * FROM foo') AS t(a int, b text, c text[])

‎contrib/dblink/sql/dblink.sql‎

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -44,6 +44,26 @@ SELECT dblink_build_sql_update('foo','1 2',2,'{"0", "a"}','{"99", "xyz"}');
4444
-- build a delete statement based on a local tuple,
4545
SELECT dblink_build_sql_delete('foo','1 2',2,'{"0", "a"}');
4646

47+
-- retest using a quoted and schema qualified table
48+
CREATESCHEMA "MySchema";
49+
CREATETABLE "MySchema"."Foo"(f1int, f2text, f3text[],primary key (f1,f2));
50+
INSERT INTO"MySchema"."Foo"VALUES (0,'a','{"a0","b0","c0"}');
51+
52+
-- list the primary key fields
53+
SELECT*
54+
FROM dblink_get_pkey('"MySchema"."Foo"');
55+
56+
-- build an insert statement based on a local tuple,
57+
-- replacing the primary key values with new ones
58+
SELECT dblink_build_sql_insert('"MySchema"."Foo"','1 2',2,'{"0", "a"}','{"99", "xyz"}');
59+
60+
-- build an update statement based on a local tuple,
61+
-- replacing the primary key values with new ones
62+
SELECT dblink_build_sql_update('"MySchema"."Foo"','1 2',2,'{"0", "a"}','{"99", "xyz"}');
63+
64+
-- build a delete statement based on a local tuple,
65+
SELECT dblink_build_sql_delete('"MySchema"."Foo"','1 2',2,'{"0", "a"}');
66+
4767
-- regular old dblink
4868
SELECT*
4969
FROM dblink('dbname=regression','SELECT * FROM foo')AS t(aint, btext, ctext[])

0 commit comments

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