Re: [GENERAL] Loop
On 12 Sep 2009, at 24:17, db.subscripti...@shepherdhill.biz wrote: Hi, I have a loop of the form: FOR rec IN SELECT code FROM staff WHERE shiftgroup = NEW.groupe ORDER BY code LOOP WHILE sdate <= NEW.todate LOOP SELECT INTO starty,endy,nday resumetime,closetime,nextday FROM shifts WHERE shift = NEW.shift; restime := sdate + starty; IF nday = true THEN clstime := sdate + interval '1 day' + endy; ELSE clstime := sdate + endy; END IF; INSERT INTO shiftsexp(id,staff,resumetime,closetime) VALUES (NEW.id,rec.code,restime,clstime); sdate := sdate + interval '1 day'; END LOOP; END LOOP; Surprisingly, the outer loop (For .. Loop) does not loop while only the WHILE ... Loop works. What makes you think that? There's nothing in your code that uses the record from your outer loop, so how do you know it doesn't loop? Did you raise notices to check? Why do you even have that loop, it doesn't appear to serve a purpose? Any hint would be appreciated. Regards, Chris. Alban Hertroys -- Screwing up is the best way to attach something to the ceiling. !DSPAM:737,4aab70c813781789017984! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Loop
On 12 Sep 2009, at 11:58, Alban Hertroys wrote: On 12 Sep 2009, at 24:17, db.subscripti...@shepherdhill.biz wrote: Hi, I have a loop of the form: FOR rec IN SELECT code FROM staff WHERE shiftgroup = NEW.groupe ORDER BY code LOOP WHILE sdate <= NEW.todate LOOP SELECT INTO starty,endy,nday resumetime,closetime,nextday FROM shifts WHERE shift = NEW.shift; restime := sdate + starty; IF nday = true THEN clstime := sdate + interval '1 day' + endy; ELSE clstime := sdate + endy; END IF; INSERT INTO shiftsexp(id,staff,resumetime,closetime) VALUES (NEW.id,rec.code,restime,clstime); sdate := sdate + interval '1 day'; END LOOP; END LOOP; Surprisingly, the outer loop (For .. Loop) does not loop while only the WHILE ... Loop works. What makes you think that? There's nothing in your code that uses the record from your outer loop, so how do you know it doesn't loop? Did you raise notices to check? Why do you even have that loop, it doesn't appear to serve a purpose? Ah wait, ENOCOFFEE... You do use the rec variable. What you don't do is reset the sdate to the value it had at the start of your outer loop, so you end up immediately meeting the condition of the WHILE expressions' loop after the first iteration through your FOR loop. So it loops through the outer loop, but after the first time there's no work left to be done inside it. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4aab752a13787908612439! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] dblink & 4 master operation
Dear all, My boss requested me that i use dblink even for a sample INSERT, now i have a INSERT query, But i don't know how to invoke insert query without select query. I have search for doc in this topic, But i just saw API ref in postgresql.org. Can u help me? Yours, Mohsen -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] dblink & 4 master operation
Dear all,
I use following command :
su -
su postgres
cd ~
psql urldump
urldump=# \i /usr/share/postgresql/8.4/contrib/dblink.sql
.
urldump=# select dblink_connect('my','dbname=urldump');
dblink_connect
OK
(1 row)
urldump=# select dblink('my','INSERT INTO ipmap (ip,username) VALUES
("127.0.0.1","mohsen");');
ERROR: column "127.0.0.1" does not exist
CONTEXT: Error occurred on dblink connection named "unnamed": could not
execute query.
urldump=#
/
I need to insert into ipmap table with ip & username columns.Please help
me...
On Sat, 2009-09-12 at 16:10 +0430, Mohsen Pahlevanzadeh wrote:
> Dear all,
>
> My boss requested me that i use dblink even for a sample INSERT, now i
> have a INSERT query, But i don't know how to invoke insert query without
> select query.
> I have search for doc in this topic, But i just saw API ref in
> postgresql.org.
> Can u help me?
>
> Yours,
> Mohsen
>
>
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Getting Out Parameter in the application using libpq
On Fri, Sep 11, 2009 at 10:30 PM, Ehsan Haq wrote: > > Hi, > I still don't get. How can I get the varchar OUT parameter in the > application? For Example > > CREATE OR REPLACE > Function getOutVarchar(outvarchar OUT varchar2) RETURN NUMBER if, from libpq: res = PQexec(conn, "SELECT * FROM getOutVarchar()"); the result should have a one column, one row result with a field called outvarchar. merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Missing 8.2.14 source rpms
Sorry if it is already known but just to be sure the 8.2.14 source rpms are missing from the yum repository. Regards, Clodoaldo -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to GRANT rights to modify a table ???
Hello, A very annoying problem with Postgres is the following one : As postgres user, I give all rights to all objects of a database or schema to a colleague : GRANT ALL ON mytable TO mycolleague; But when he tries to modify something, even something really small like adding a column to a table : ALTER TABLE mytable ADD COLUMN field integer; ERROR: must be the owner of the mytable relation Therefore, how to give right to some user on tables structure ??? Thanks a lot for any help, this problem is always giving us lots of difficulties... Denis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to GRANT rights to modify a table ???
On 12/09/2009 16:44, Denis BUCHER wrote: > Hello, > > A very annoying problem with Postgres is the following one : > > As postgres user, I give all rights to all objects of a database or > schema to a colleague : > GRANT ALL ON mytable TO mycolleague; > > But when he tries to modify something, even something really small like > adding a column to a table : > ALTER TABLE mytable ADD COLUMN field integer; > ERROR: must be the owner of the mytable relation The clue is in the error message :-) GRANTing ALL doesn't make them the owner of the object as well. For that, you have to do something like ALTER TABLE mytable OWNER TO mycolleague; - I forget the exact syntax, but it'll be in the docs. Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland r...@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to GRANT rights to modify a table ???
"Raymond O'Donnell" writes: > On 12/09/2009 16:44, Denis BUCHER wrote: >> ERROR: must be the owner of the mytable relation > The clue is in the error message :-) GRANTing ALL doesn't make them > the owner of the object as well. > For that, you have to do something like > ALTER TABLE mytable OWNER TO mycolleague; What might be more practical is to create a group role that actually owns the table, and make everyone who needs to do this sort of thing a member of that group. CREATE ROLE mytable_owner; ALTER TABLE mytable OWNER TO mytable_owner; GRANT mytable_owner TO myself, mycolleague, ... ; I highly recommend reading the GRANT command reference page. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] primary keys
Hi Folks I'm looking for a bit of advice regarding alpha primary keys. I have a table (designed by someone else) that has a numeric primary key and also a unique non-null email address field. The use of the primary key is causing me some headaches in that in multiple database server environment each server allocates a unique number from a range (and that works fine) but when the table is replicated (master-master- master) the exception handling is a bit tricky because each database server may have records that are duplicate at the email address field - with a different primary key number. I don't know why it was done this way but it seems to me that the email addresses are unique, non null and could be used as the primary key. This would make the replication much faster and simpler. Does anyone out there think the change (number to email address as primary key) would be a bad idea ? your thoughts would be appreciated. regards Grant -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to GRANT rights to modify a table ???
Tom Lane a écrit : >>> ERROR: must be the owner of the mytable relation > >> The clue is in the error message :-) GRANTing ALL doesn't make them >> the owner of the object as well. > >> For that, you have to do something like >> ALTER TABLE mytable OWNER TO mycolleague; > > What might be more practical is to create a group role that actually > owns the table, and make everyone who needs to do this sort of thing > a member of that group. > > CREATE ROLE mytable_owner; > ALTER TABLE mytable OWNER TO mytable_owner; > GRANT mytable_owner TO myself, mycolleague, ... ; > > I highly recommend reading the GRANT command reference page. > > regards, tom lane That's great, in fact this is the right solution, I didn't think about it... Thanks a lot... Denis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to GRANT rights to modify a table ???
Hello, Tom Lane a écrit : > "Raymond O'Donnell" writes: >> On 12/09/2009 16:44, Denis BUCHER wrote: >>> ERROR: must be the owner of the mytable relation > >> The clue is in the error message :-) GRANTing ALL doesn't make them >> the owner of the object as well. > >> For that, you have to do something like >> ALTER TABLE mytable OWNER TO mycolleague; > > What might be more practical is to create a group role that actually > owns the table, and make everyone who needs to do this sort of thing > a member of that group. > > CREATE ROLE mytable_owner; > ALTER TABLE mytable OWNER TO mytable_owner; > GRANT mytable_owner TO myself, mycolleague, ... ; > > I highly recommend reading the GRANT command reference page. > > regards, tom lane It's strange but after having tried it, it doesn't work ? pg_dump says : CREATE ROLE tableowner; ALTER ROLE tableowner WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB NOLOGIN; GRANT tableowner TO mycolleague GRANTED BY postgres; GRANT tableowner TO postgres GRANTED BY postgres; My table seems to be correct : bw_rma=# \dt abc.testtable Liste des relations Schéma |Nom | Type | Propriétaire ++---+-- abc| testtable | table | tableowner (1 ligne) When I login as "mycolleague" : ALTER TABLE abc.testtable ADD COLUMN field integer; What is wrong in what I do ? Thanks a lot for any help... Denis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to GRANT rights to modify a table ???
Denis BUCHER a écrit : > Tom Lane a écrit : >> "Raymond O'Donnell" writes: >>> On 12/09/2009 16:44, Denis BUCHER wrote: ERROR: must be the owner of the mytable relation >>> The clue is in the error message :-) GRANTing ALL doesn't make them >>> the owner of the object as well. >>> For that, you have to do something like >>> ALTER TABLE mytable OWNER TO mycolleague; >> What might be more practical is to create a group role that actually >> owns the table, and make everyone who needs to do this sort of thing >> a member of that group. >> >> CREATE ROLE mytable_owner; >> ALTER TABLE mytable OWNER TO mytable_owner; >> GRANT mytable_owner TO myself, mycolleague, ... ; >> >> I highly recommend reading the GRANT command reference page. >> >> regards, tom lane > > It's strange but after having tried it, it doesn't work ? > > pg_dump says : > > CREATE ROLE tableowner; > ALTER ROLE tableowner WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB > NOLOGIN; > GRANT tableowner TO mycolleague GRANTED BY postgres; > GRANT tableowner TO postgres GRANTED BY postgres; > > My table seems to be correct : > > bw_rma=# \dt abc.testtable > Liste des relations > Schéma |Nom | Type | Propriétaire > ++---+-- > abc| testtable | table | tableowner > (1 ligne) > > When I login as "mycolleague" : > ALTER TABLE abc.testtable ADD COLUMN field integer; > > What is wrong in what I do ? Ok I found the solution myself ;-)) ALTER ROLE mycolleague INHERIT; That's all ;-)) Denis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to GRANT rights to modify a table ???
Denis BUCHER writes: > Tom Lane a écrit : >> What might be more practical is to create a group role that actually >> owns the table, and make everyone who needs to do this sort of thing >> a member of that group. > It's strange but after having tried it, it doesn't work ? Hmm, worksforme: regression=# create table t1 (f1 int); CREATE TABLE regression=# create role t1o ; CREATE ROLE regression=# alter table t1 owner to t1o; ALTER TABLE regression=# create user joe; CREATE ROLE regression=# grant t1o to joe; GRANT ROLE regression=# \c - joe psql (8.5devel) You are now connected to database "regression" as user "joe". regression=> alter table t1 add column f2 int; ALTER TABLE regression=> One possibility is that your "mycolleague" user is set up without the INHERIT property, in which case he'd have to do "SET ROLE tableowner" to acquire the ownership privilege. (tableowner's INHERIT setting is not the relevant thing here; mycolleague's is.) regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] primary keys
On Sat, Sep 12, 2009 at 12:35 PM, Grant Maxwell wrote: > Hi Folks > > I'm looking for a bit of advice regarding alpha primary keys. > > I have a table (designed by someone else) that has a numeric primary key and > also a unique non-null email address field. > > The use of the primary key is causing me some headaches in that in multiple > database server environment each server allocates a unique number from a > range > (and that works fine) but when the table is replicated > (master-master-master) the exception handling is a bit tricky because each > database server may have > records that are duplicate at the email address field - with a different > primary key number. > > I don't know why it was done this way but it seems to me that the email > addresses are unique, non null and could be used as the primary key. This > would make the > replication much faster and simpler. > > Does anyone out there think the change (number to email address as primary > key) would be a bad idea ? > > your thoughts would be appreciated. I think it's a fine idea so long as you understand: *) the index(es) will be larger *) referring tables will have to be updated (via RI) if/when the email address changes *) lots of people will tell you that you are doing it wrong :-) *) if you need to specialize p-key further (add another field), it can be a big change on the plus side: *) you get to drop an index because you obviously had to index the key serparately *) if you query a referring table and are interested in email address (and no dependent props), you get to skip a join *) sorting be email address can be free *) less data transfer headaches merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] primary keys
On Sun, 13 Sep 2009 02:35:02 +1000 Grant Maxwell wrote: > Hi Folks > > I'm looking for a bit of advice regarding alpha primary keys. > > I have a table (designed by someone else) that has a numeric primary > key and also a unique non-null email address field. > > The use of the primary key is causing me some headaches in that in > multiple database server environment each server allocates a unique > number from a range > (and that works fine) but when the table is replicated (master-master- > master) the exception handling is a bit tricky because each database > server may have > records that are duplicate at the email address field - with a > different primary key number. > > I don't know why it was done this way but it seems to me that the > email addresses are unique, non null and could be used as the primary > key. This would make the > replication much faster and simpler. > > Does anyone out there think the change (number to email address as > primary key) would be a bad idea ? > > your thoughts would be appreciated. I think you have two issues here, and if you identify them as such, you'll come up with a better decision. The first is, "Should the email address be my primary key". And the answer to that really depends on whether you expect it to change a lot, whether you have a lot of FK relations and how easy/difficult it will be to manage those. The second, which may influence the first, is "how do I manage conflicts when data from different sources is merged?" which is a huge, complex question. One potential answer is to replace your integer surrogate key with a GUID key, which will be unique from all the different sources. You could also use a "prefix" system to ensure uniqueness. Another option is to use the email address itself. I'm surprised by your approach, as personally, I've seen a LOT of people who share a single email address (husband/wife, for example). It's not horribly common, since getting addresses is easy, but it happens, and it's something to consider when setting this up: if two people share an email address and try to add their records at different places, how will the system handle it? In any event, if all those factors tell you that you should switch to using the email address as the PK, I doubt you'll regret your decision. Just be sure to take into account the foreign key factor early on and you shouldn't have too many problems (in my experience, a lot of people are unaware of ON DELETE CASCADE and ON UPDATE CASCADE). Hope this helps. -Bill -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] primary keys
On Sat, Sep 12, 2009 at 2:11 PM, Bill Moran wrote: > On Sun, 13 Sep 2009 02:35:02 +1000 > Grant Maxwell wrote: > >> Hi Folks >> >> I'm looking for a bit of advice regarding alpha primary keys. >> >> I have a table (designed by someone else) that has a numeric primary >> key and also a unique non-null email address field. >> >> The use of the primary key is causing me some headaches in that in >> multiple database server environment each server allocates a unique >> number from a range >> (and that works fine) but when the table is replicated (master-master- >> master) the exception handling is a bit tricky because each database >> server may have >> records that are duplicate at the email address field - with a >> different primary key number. >> >> I don't know why it was done this way but it seems to me that the >> email addresses are unique, non null and could be used as the primary >> key. This would make the >> replication much faster and simpler. >> >> Does anyone out there think the change (number to email address as >> primary key) would be a bad idea ? >> >> your thoughts would be appreciated. > > I think you have two issues here, and if you identify them as such, you'll > come up with a better decision. > > The first is, "Should the email address be my primary key". And the answer > to that really depends on whether you expect it to change a lot, whether you > have a lot of FK relations and how easy/difficult it will be to manage those. > > The second, which may influence the first, is "how do I manage conflicts > when data from different sources is merged?" which is a huge, complex > question. One potential answer is to replace your integer surrogate key > with a GUID key, which will be unique from all the different sources. You guid solves the surrogate issue wrt data transfer, but glosses over what happens when you have duplicates. > could also use a "prefix" system to ensure uniqueness. Another option is > to use the email address itself. I'm surprised by your approach, as > personally, I've seen a LOT of people who share a single email address > (husband/wife, for example). It's not horribly common, since getting that's going to depend on how it's defined in the app. couple of different approaches: email _must_ be unique, and we care not to whom it belongs: email (email primary key) contact (email references email [...]) we do care option 1: email (email, memo text, primary key(email, memo)) contact (email, memo, references email(email, memo)) storing info in memo field to distinguish the different users we do care option 2: email (email primary key) contact (contact_id) email_contact_map ( contact_id references contact, email references email, memo, -- 'person a', 'person b', etc primary key(contact_id, email), ) The point is this: if you (the user) needs to distinguish between email adress users, that information should be in the database...allowing multiple entry of email addresses via serial or guid allows you to sneak by this requirement merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] primary keys
On 13/09/2009, at 2:46 AM, Tom Lane wrote: Grant Maxwell writes: I don't know why it was done this way but it seems to me that the email addresses are unique, non null and could be used as the primary key. This would make the replication much faster and simpler. Does anyone out there think the change (number to email address as primary key) would be a bad idea ? One thing that's often considered a useful attribute of a primary key is that it be immutable. In your application, do users ever change their email addresses? If so, what should happen --- is it okay to treat that as effectively a new entry? This would be ok. The table keeps a list of email addresses and some stats on them users may have multiple addresses but would never alter a specific record. Also, if you have any other tables referencing this one via foreign keys, you'd have to have them storing the email address instead of the serial number; it'll be bulkier and address updates will be that much more expensive. so text PKs will be less efficient than numeric ? You can find lots and lots and lots of discussion of this topic if you search the archives for talk about natural versus surrogate keys. Good pointer = thanks Tom regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] primary keys
Grant Maxwell writes: > I don't know why it was done this way but it seems to me that the > email addresses are unique, non null and could be used as the primary > key. This would make the replication much faster and simpler. > Does anyone out there think the change (number to email address as > primary key) would be a bad idea ? One thing that's often considered a useful attribute of a primary key is that it be immutable. In your application, do users ever change their email addresses? If so, what should happen --- is it okay to treat that as effectively a new entry? Also, if you have any other tables referencing this one via foreign keys, you'd have to have them storing the email address instead of the serial number; it'll be bulkier and address updates will be that much more expensive. You can find lots and lots and lots of discussion of this topic if you search the archives for talk about natural versus surrogate keys. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Missing 8.2.14 source rpms
On Sat, 2009-09-12 at 12:22 -0300, Clodoaldo Neto wrote: > Sorry if it is already known but just to be sure the 8.2.14 source > rpms are missing from the yum repository. Because of bandwith issues on the master RPM build server, srpms are uploaded a few days after the binary packages are uploaded. Nowadays I am still working on the repository -- pushing new packages while the servers are hot. So, you will probably find SRPMs on Monday. OTOH, anyone can build SRPMs on their machines. Here is a quick quide: svn co https://projects.commandprompt.com/public/pgcore/repo/ cd rpm/redhat/$PG_VERSION/$PACKAGE_NAME/$DISTROVER make srpm (or make build for srpm+rpm) You can edit .spec file and build your custom package with this way) -HTH. Regards, -- Devrim GÜNDÜZ, RHCE Command Prompt - http://www.CommandPrompt.com devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org signature.asc Description: This is a digitally signed message part
Re: [GENERAL] Missing 8.2.14 source rpms
2009/9/12 Devrim GÜNDÜZ : > On Sat, 2009-09-12 at 12:22 -0300, Clodoaldo Neto wrote: > >> Sorry if it is already known but just to be sure the 8.2.14 source >> rpms are missing from the yum repository. > > Because of bandwith issues on the master RPM build server, srpms are > uploaded a few days after the binary packages are uploaded. > > Nowadays I am still working on the repository -- pushing new packages > while the servers are hot. So, you will probably find SRPMs on Monday. That is good for me. Thanks. Clodoaldo. > > OTOH, anyone can build SRPMs on their machines. Here is a quick quide: > > svn co https://projects.commandprompt.com/public/pgcore/repo/ > cd rpm/redhat/$PG_VERSION/$PACKAGE_NAME/$DISTROVER > make srpm > > (or make build for srpm+rpm) > > You can edit .spec file and build your custom package with this way) > > -HTH. > > Regards, > -- > Devrim GÜNDÜZ, RHCE > Command Prompt - http://www.CommandPrompt.com > devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr > http://www.gunduz.org > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Getting Out Parameter in the application using libpq
Hi,
First of all the below mentioned function can't be called with "SELECT *
FROM getOutVarchar()". Since the Function signature does not match.
CREATE OR REPLACE
Function getOutVarchar(outvarchar OUT varchar2) RETURN NUMBER
IS
BEGIN
outvarchar:='This is Out String';
RETURN 1;
END getOutVarchar;
However calling the above function with "SELECT * FROM getOutVarchar('abc');"
does returns a Single column in a single row with a field named 'outvarchar'
but the value of the field is "1" which is obvious due to "RETURN 1" and not
"This is Out String".
Thanks
Ehsan
--- On Sat, 9/12/09, Merlin Moncure wrote:
From: Merlin Moncure
Subject: Re: [GENERAL] Getting Out Parameter in the application using libpq
To: "Ehsan Haq"
Cc: pgsql-general@postgresql.org
Date: Saturday, September 12, 2009, 1:21 PM
On Fri, Sep 11, 2009 at 10:30 PM, Ehsan Haq wrote:
>
> Hi,
> I still don't get. How can I get the varchar OUT parameter in the
> application? For Example
>
> CREATE OR REPLACE
> Function getOutVarchar(outvarchar OUT varchar2) RETURN NUMBER
if, from libpq:
res = PQexec(conn, "SELECT * FROM getOutVarchar()");
the result should have a one column, one row result with a field
called outvarchar.
merlin
