Skip to content

Commit 5d841e0

Browse files
author
James William Pye
committed
Fixes stored procedure invocation statement generation.
Bug 1010620 reported by Dallas Morisette. The former implementation depended on regtype's string represention matching the type identifiers. This was not the case for varchar as it was rendered as 'character varying'. Statement generation could have been corrected while using that method, but it would have meant including exception cases. So, in order to encourage simplicity here, use fully-qualified type identifiers to reference the parameter types.
1 parent ea780ac commit 5d841e0

4 files changed

Lines changed: 34 additions & 22 deletions

File tree

‎postgresql/driver/pq3.py‎

Lines changed: 6 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1214,13 +1214,17 @@ def __init__(self, ident, database, description = ()):
12141214
if an is not None:
12151215
self._input_attmap[an] = x
12161216

1217-
proargs = proctup['_proargs']
1217+
proargs = proctup['proargtypes']
1218+
for x in proargs:
1219+
# get metadata filled out.
1220+
database.typio.resolve(x)
1221+
12181222
self.statement = database.prepare(
12191223
"SELECT * FROM %s(%s) AS func%s" %(
12201224
proctup['_proid'],
12211225
# ($1::type, $2::type, ... $n::type)
12221226
', '.join([
1223-
'$%d::%s' %(x + 1, proargs[x])
1227+
'$%d::%s' %(x + 1, database.typio.sql_type_from_oid(proargs[x]))
12241228
for x in range(len(proargs))
12251229
]),
12261230
# Description for anonymous record returns

‎postgresql/lib/sys.py‎

Lines changed: 2 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -55,14 +55,8 @@
5555
pg_proc.*,
5656
pg_proc.oid::regproc AS _proid,
5757
pg_proc.oid::regprocedure as procedure_id,
58-
-- mm, the pain. the sweet, sweet pain. oh it's portable.
59-
-- it's so portable that it runs on BDB on win32.
60-
COALESCE(string_to_array(trim(textin(array_out(string_to_array(
61-
replace(
62-
trim(textin(oidvectorout(proargtypes)), '{}'),
63-
',', ' '
64-
), ' ')::oid[]::regtype[])), '{}'), ',')::text[], '{}'::text[])
65-
AS _proargs,
58+
COALESCE(string_to_array(trim(replace(textin(oidvectorout(proargtypes)), ',', ' '), '{}'), ' ')::oid[], '{}'::oid[])
59+
AS proargtypes,
6660
(pg_type.oid = 'record'::regtype or pg_type.typtype = 'c') AS composite
6761
FROM
6862
pg_catalog.pg_proc LEFT JOIN pg_catalog.pg_type ON (

‎postgresql/protocol/typio.py‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -763,7 +763,7 @@ def sql_type_from_oid(self, oid):
763763
if oid in self.typmeta:
764764
nsp, name, *_ = self.typmeta[oid]
765765
return pg_str.quote_ident(nsp) + '.' + pg_str.quote_ident(name)
766-
return pg_types.oid_to_name.get(oid)
766+
return 'pg_catalog.' + pg_types.oid_to_name.get(oid)
767767

768768
def type_from_oid(self, oid):
769769
typ = pg_types.oid_to_type.get(oid)

‎postgresql/test/test_driver.py‎

Lines changed: 25 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -457,37 +457,37 @@ def testStatementAndCursorMetadata(self):
457457

458458
ps = self.db.prepare("SELECT $1::text AS my_text_column")
459459
self.failUnlessEqual(tuple(ps.column_names), ('my_text_column',))
460-
self.failUnlessEqual(tuple(ps.sql_column_types), ('text',))
461-
self.failUnlessEqual(tuple(ps.sql_parameter_types), ('text',))
460+
self.failUnlessEqual(tuple(ps.sql_column_types), ('pg_catalog.text',))
461+
self.failUnlessEqual(tuple(ps.sql_parameter_types), ('pg_catalog.text',))
462462
self.failUnlessEqual(tuple(ps.pg_parameter_types), (pg_types.TEXTOID,))
463463
self.failUnlessEqual(tuple(ps.column_types), (str,))
464464
self.failUnlessEqual(tuple(ps.parameter_types), (str,))
465465
c = ps.declare('textdata')
466466
self.failUnlessEqual(tuple(c.column_names), ('my_text_column',))
467-
self.failUnlessEqual(tuple(c.sql_column_types), ('text',))
467+
self.failUnlessEqual(tuple(c.sql_column_types), ('pg_catalog.text',))
468468
self.failUnlessEqual(tuple(c.pg_column_types), (pg_types.TEXTOID,))
469469
self.failUnlessEqual(tuple(c.column_types), (str,))
470470

471471
ps = self.db.prepare("SELECT $1::text AS my_column1, $2::varchar AS my_column2")
472472
self.failUnlessEqual(tuple(ps.column_names), ('my_column1','my_column2'))
473-
self.failUnlessEqual(tuple(ps.sql_column_types), ('text', 'CHARACTER VARYING'))
474-
self.failUnlessEqual(tuple(ps.sql_parameter_types), ('text', 'CHARACTER VARYING'))
473+
self.failUnlessEqual(tuple(ps.sql_column_types), ('pg_catalog.text', 'CHARACTER VARYING'))
474+
self.failUnlessEqual(tuple(ps.sql_parameter_types), ('pg_catalog.text', 'CHARACTER VARYING'))
475475
self.failUnlessEqual(tuple(ps.pg_parameter_types), (pg_types.TEXTOID, pg_types.VARCHAROID))
476476
self.failUnlessEqual(tuple(ps.pg_column_types), (pg_types.TEXTOID, pg_types.VARCHAROID))
477477
self.failUnlessEqual(tuple(ps.parameter_types), (str,str))
478478
self.failUnlessEqual(tuple(ps.column_types), (str,str))
479479
c = ps.declare('textdata', 'varchardata')
480480
self.failUnlessEqual(tuple(c.column_names), ('my_column1','my_column2'))
481-
self.failUnlessEqual(tuple(c.sql_column_types), ('text', 'CHARACTER VARYING'))
481+
self.failUnlessEqual(tuple(c.sql_column_types), ('pg_catalog.text', 'CHARACTER VARYING'))
482482
self.failUnlessEqual(tuple(c.pg_column_types), (pg_types.TEXTOID, pg_types.VARCHAROID))
483483
self.failUnlessEqual(tuple(c.column_types), (str,str))
484484

485485
self.db.execute("CREATE TYPE public.myudt AS (i int)")
486486
myudt_oid = self.db.prepare("select oid from pg_type WHERE typname='myudt'").first()
487487
ps = self.db.prepare("SELECT $1::text AS my_column1, $2::varchar AS my_column2, $3::public.myudt AS my_column3")
488488
self.failUnlessEqual(tuple(ps.column_names), ('my_column1','my_column2', 'my_column3'))
489-
self.failUnlessEqual(tuple(ps.sql_column_types), ('text', 'CHARACTER VARYING', 'public.myudt'))
490-
self.failUnlessEqual(tuple(ps.sql_parameter_types), ('text', 'CHARACTER VARYING', 'public.myudt'))
489+
self.failUnlessEqual(tuple(ps.sql_column_types), ('pg_catalog.text', 'CHARACTER VARYING', 'public.myudt'))
490+
self.failUnlessEqual(tuple(ps.sql_parameter_types), ('pg_catalog.text', 'CHARACTER VARYING', 'public.myudt'))
491491
self.failUnlessEqual(tuple(ps.pg_column_types), (
492492
pg_types.TEXTOID, pg_types.VARCHAROID, myudt_oid)
493493
)
@@ -498,7 +498,7 @@ def testStatementAndCursorMetadata(self):
498498
self.failUnlessEqual(tuple(ps.column_types), (str,str,tuple))
499499
c = ps.declare('textdata', 'varchardata', (123,))
500500
self.failUnlessEqual(tuple(c.column_names), ('my_column1','my_column2', 'my_column3'))
501-
self.failUnlessEqual(tuple(c.sql_column_types), ('text', 'CHARACTER VARYING', 'public.myudt'))
501+
self.failUnlessEqual(tuple(c.sql_column_types), ('pg_catalog.text', 'CHARACTER VARYING', 'public.myudt'))
502502
self.failUnlessEqual(tuple(c.pg_column_types), (
503503
pg_types.TEXTOID, pg_types.VARCHAROID, myudt_oid
504504
))
@@ -646,8 +646,22 @@ def testProcExecution(self):
646646
"CREATE OR REPLACE FUNCTION ifoo(int) RETURNS int LANGUAGE SQL AS 'select $1'"
647647
)
648648
ifoo = self.db.proc('ifoo(int)')
649-
self.failUnless(ifoo(1) == 1)
650-
self.failUnless(ifoo(None) is None)
649+
self.failUnlessEqual(ifoo(1), 1)
650+
self.failUnlessEqual(ifoo(None), None)
651+
self.db.execute(
652+
"CREATE OR REPLACE FUNCTION ifoo(varchar) RETURNS varchar LANGUAGE SQL AS 'select $1'"
653+
)
654+
ifoo = self.db.proc('ifoo(varchar)')
655+
self.failUnlessEqual(ifoo('1'), '1')
656+
self.failUnlessEqual(ifoo(None), None)
657+
self.db.execute(
658+
"CREATE OR REPLACE FUNCTION ifoo(varchar,int) RETURNS varchar LANGUAGE SQL AS 'select ($1::int + $2)::varchar'"
659+
)
660+
ifoo = self.db.proc('ifoo(varchar,int)')
661+
self.failUnlessEqual(ifoo('1',1), '2')
662+
self.failUnlessEqual(ifoo(None,1), None)
663+
self.failUnlessEqual(ifoo('1',None), None)
664+
self.failUnlessEqual(ifoo('2',2), '4')
651665

652666
def testProcExecutionInXact(self):
653667
with self.db.xact():

0 commit comments

Comments
 (0)