Postgres Pro
Downloads
Home   >   mailing lists

Thread: YNT: Re: [SQL] Using bind variable within BEGIN END

YNT: Re: [SQL] Using bind variable within BEGIN END

From
gulsumramazanoglu
Date:
... which will pass arguments and get back a result set, i mean.. 



Samsung cihazımdan gönderildi


-------- Orjinal mesaj --------
Kimden: "David G. Johnston" <david.g.johnston@gmail.com>
Tarih: 2 06 2017 9:21 PM (GMT+02:00)
Alıcı: anand086 <anand086@gmail.com>
Cc: pgsql-sql@postgresql.org
Konu: Re: [SQL] Using bind variable within BEGIN END

On Friday, June 2, 2017, anand086 <anand086@gmail.com> wrote:
Another example where we are getting the same error is from the call of the
below code --

ctx.update(""
                                                                + "begin \n"
                                                                + "
access.register_type( \n"
                                                                + "
p_entity_system                 => ?, \n"
                                                                + "
p_entity_type                      => ?, \n"
                                                                + "
p_attribute_name             => ?, \n"
                                                                + "
p_creator_id                        => ?, \n"
                                                                + "
p_description                       => ? \n"
                                                                + " );"
                                                                + "end;",
                                                                systemName,
                                                                entityType,

attributeName,
                                                                creatorID,

attributeDescription);


access.register_type is a function


PostgreSQL doesn't alllow named arguments when calling functions and function calls must be part of a SQL statement: select func(?,?,?,?.?);  this applied even to functions that do not return results.  IOW, PostgreSQL doesn't have stored procedures.

David J.

Re: YNT: Re: [SQL] Using bind variable within BEGIN END

From
Adrian Klaver
Date:
On 06/02/2017 11:38 AM, gulsumramazanoglu wrote:
> ... which will pass arguments and get back a result set, i mean..

Using Python and psycopg2:

\df billing_days                                     List of functions Schema |     Name     |     Result data type
|     Argument data 
 
types       |  Type
--------+--------------+--------------------------+--------------------------------+-------- public | billing_days |
TABLE(billing_datedate) | start_date date, 
 
end_date date | normal


select * from billing_days('05/01/2017', '05/30/2017'); billing_date
-------------- 2017-05-01 2017-05-02 2017-05-03 2017-05-04 2017-05-05 2017-05-08 2017-05-09 2017-05-10 2017-05-11
2017-05-122017-05-15 2017-05-16 2017-05-17 2017-05-18 2017-05-19 2017-05-22 2017-05-23 2017-05-24 2017-05-25 2017-05-26
2017-05-292017-05-30
 


import psycopg2
con = psycopg2.connect('dbname=**** user=***_admin host=localhost')
bill_cur = con.cursor()
bill_cur.execute("SELECT * FROM billing_days(%s, %s)" , ('05/01/2017', 
'05/31/2017'))
bill_rs = bill_cur.fetchall()
for row in bill_rs:    print(row[0])

2017-05-01
2017-05-02
2017-05-03
2017-05-04
2017-05-05
2017-05-08
2017-05-09
2017-05-10
2017-05-11
2017-05-12
2017-05-15
2017-05-16
2017-05-17
2017-05-18
2017-05-19
2017-05-22
2017-05-23
2017-05-24
2017-05-25
2017-05-26
2017-05-29
2017-05-30
2017-05-31

> 
> 
> 
> Samsung cihazımdan gönderildi


-- 
Adrian Klaver
adrian.klaver@aklaver.com