agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedYNT: Re: Using bind variable within BEGIN END
3+ messages / 2 participants
[nested] [flat]
* YNT: Re: Using bind variable within BEGIN END
@ 2017-06-02 18:35 gulsumramazanoglu <gulsumramazanoglu@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: gulsumramazanoglu @ 2017-06-02 18:35 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; anand086 <anand086@gmail.com>; +Cc: pgsql-sql
David hi, if PostgreSQL doesnt have stored procedures and function can only be used by a SQL command, so we cannot make a call to an SQL script (of any kind, name it procedure or something) directly from a HLL program.. do i understand correctly?
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* YNT: Re: Using bind variable within BEGIN END
@ 2017-06-02 18:38 gulsumramazanoglu <gulsumramazanoglu@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: gulsumramazanoglu @ 2017-06-02 18:38 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; anand086 <anand086@gmail.com>; +Cc: pgsql-sql
... 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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: YNT: Re: Using bind variable within BEGIN END
@ 2017-06-02 20:00 Adrian Klaver <adrian.klaver@aklaver.com>
parent: gulsumramazanoglu <gulsumramazanoglu@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Adrian Klaver @ 2017-06-02 20:00 UTC (permalink / raw)
To: gulsumramazanoglu <gulsumramazanoglu@gmail.com>; David G. Johnston <david.g.johnston@gmail.com>; anand086 <anand086@gmail.com>; +Cc: pgsql-sql
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_date date) | 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-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
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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2017-06-02 20:00 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-06-02 18:35 YNT: Re: Using bind variable within BEGIN END gulsumramazanoglu <gulsumramazanoglu@gmail.com>
2017-06-02 18:38 YNT: Re: Using bind variable within BEGIN END gulsumramazanoglu <gulsumramazanoglu@gmail.com>
2017-06-02 20:00 ` Adrian Klaver <adrian.klaver@aklaver.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox