[GENERAL] postgres
all Any kind of parameter in solaris root section need to be checked before install postgres in Solaris thanks Amy
Re: [GENERAL] Optimistic locking with multiple rows
On 6/01/2010 10:53 PM, John T. Dow wrote: I posted this several days ago to pgsql-jdbc but have had no response. I am posting it here (with minor changes in the wording). I have developed some code that works, I'm just not sure I have the best solution. I have applications in which the user can create a read-only resultset with multiple rows. For example, customers who are 90 days in arrears might be brought up for review. The user might scroll through the rows reviewing the data, and then he might decide to update one of them. A second query is used to update that one row. At the time of the update, the current contents of that row is reread FOR UPDATE and compared against the original row. If they differ, someone else has altered the row after the resultset was created. The user is informed that another user has changed the row in question; he can then decide to accept the changes he has made or leave in place the changes made by the other user. In either case, that row in the original resultset has to be made to match the current contents in the table, because the user might scroll back and forth and revisit it. I am using refreshRow() to make that row current, but the problem is that refreshRow() can be extremely slow. I create the read-only, multiple row resultset (ie viewResultSet) like this: createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); viewResultSet = jdbcStmt.executeQuery(SELECT ... FROM ... WHERE select multiple rows); Scroll through the resultset to view rows as desired. When positioned at a row, can update that row. See below. Can you instead create an explicit, named updatable cursor? Then FETCH from it, and UPDATE ... WHERE CURRENT OF it? That way you won't have to do all this kludging with refreshing rowsets. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] postgres issue
all For dba, do you create a dba user and grant all privilege to 'dba user', so you can create table, etc ? For a team, do you create user for all team member and tie to a group ? Not sure the set up for postgres also what is best way to create a production and test region using the same database ? How to use mount for differnt region. any help is appreciate. Amy
[GENERAL] R: aggregate over tables in different schema
Try using inheritance. Il giorno 10 gen, 2010 2:30 m., Ivan Sergio Borgonovo m...@webthatworks.it ha scritto: I've tables in different schemas all with the same name and structure. I'd like to compute an aggregate on the union of those tables. I don't know the schemas in advance. The list of the schema will be built selecting all the schemas that contain a table with that name. Other than building dynamically the statement as a list of union or building up a view is there any other way? -- Ivan Sergio Borgonovo http://www.webthatworks.it -- 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] postgres
On 9/01/2010 11:23 PM, Amy Smith wrote: all Any kind of parameter in solaris root section need to be checked before install postgres in Solaris Assuming you're asking if any kernel parameters need tuning before installing PostgreSQL on a solaris system: not really, no. However, you may need to adjust shared memory limits if you intend to increase shared_buffers (which you should generally, for better performance). Please see the installation section of the manual for details. -- Craig Ringer -- 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] postgres issue
On 9/01/2010 10:53 PM, Amy Smith wrote: all For dba, do you create a dba user and grant all privilege to 'dba user', so you can create table, etc ? For a team, do you create user for all team member and tie to a group ? Not sure the set up for postgres However you prefer, really. I prefer to create a role that owns a particular database and GRANT people who administrate that database access to that role, but you can just as easily do DBA work as a superuser if you trust your DBAs to access _all_ your databases. also what is best way to create a production and test region using the same database ? If possible, don't. Use separate databases in the same PostgreSQL instance or even a different instance. How to use mount for differnt region. I do not understand this question. Can you explain what you mean a bit more? -- Craig Ringer -- 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] An issue with max() and order by ... limit 1 in postgresql8.3-beta3
Thanks for your guys' help. I did not know the meaning of betas / alphas things before and just simply downloaded one to use. Now my 8.3 -beta3 version is really in production and get 100 rows of monitoring data per minutes. So far so good. Anyway, I will upgrade it to a latest stable version. With Andreas's example query: select distinct on (sid) sid, data, date from test order by sid, data desc, date; I rewrite my query. It just take 20 seconds to finish the search and is much better than my old query which takes 400 seconds to return the results. Thanks again for Andreas's example query. For reference, the following is my new query (I create an index for two fileds (rd.sensor_id,rd.sensor_channel)). Thanks a lot gain. Ouyang # select rt_data.r_flowmeter_caliber as r_flowmeter_caliber, rt_data.r_max01_sloc as r_max01_sloc, rt_data.r_max01_sdata as r_max01_sdata, rt_data.r_max01_sdate as r_max01_sdate, rt_data.r_min01_sdata as r_min01_sdata, rt_data.r_min01_sdate as r_min01_sdate, rt_data.r_avg01_sdata as r_avg01_sdata, acc_data.r_end_sdate as r_end_sdate, acc_data.r_end_sdata as r_end_sdata, acc_data.r_start_sdate as r_start_sdate, acc_data.r_start_sdata as r_start_sdata, acc_data.r_acc_sdata as r_acc_sdata from ( select ec.flowmeter_caliber as r_flowmeter_caliber, max01.r_sloc as r_max01_sloc, round(max01.r_sdata*100)/100 as r_max01_sdata, max01.r_sdate as r_max01_sdate, round(min01.r_sdata*100)/100 as r_min01_sdata, min01.r_sdate as r_min01_sdate, round(avg01.r_sdata*100)/100 as r_avg01_sdata, max01.r_channel as r_channel, max01.r_sid as r_sid, max01.r_sloc as r_sloc from (select distinct on (rd.sensor_id,rd.sensor_channel) rd.sensor_id as r_sid, rd.sensor_channel as r_channel, rd.sensor_data as r_sdata, rd.sensor_date as r_sdate, sc.external_ins as r_sloc from record_data rd, sensor_cfg sc, energy_classification02 ec where rd.sensor_date between '2009-08-01' and '2010-01-08' and sc.sensor_id = rd.sensor_id and sc.external_ins=ec.measure_name and sc.channel = ec.instantaneous_channel and sc.channel = rd.sensor_channel and sc.remarks='瞬时值' and ec.flowmeter_caliber='流量' order by rd.sensor_id,rd.sensor_channel, rd.sensor_data DESC, rd.sensor_date ) max01, ( select distinct on (rd.sensor_id,rd.sensor_channel) rd.sensor_id as r_sid, rd.sensor_channel as r_channel, rd.sensor_data as r_sdata, rd.sensor_date as r_sdate, sc.external_ins as r_sloc from record_data rd, sensor_cfg sc, energy_classification02 ec where rd.sensor_date between '2009-08-01' and '2010-01-08' and sc.sensor_id = rd.sensor_id and sc.external_ins=ec.measure_name and sc.channel = ec.instantaneous_channel and sc.channel = rd.sensor_channel and sc.remarks='瞬时值' and ec.flowmeter_caliber='流量' order by rd.sensor_id,rd.sensor_channel, rd.sensor_data ASC, rd.sensor_date ) min01, ( select avg(rd01.sensor_data) as r_sdata, rd01.sensor_id as r_sid, rd01.sensor_channel as r_channel from record_data rd01, sensor_cfg sc, energy_classification02 ec where rd01.sensor_date between '2009-08-01' and '2010-01-08' and sc.sensor_id = rd01.sensor_id and sc.external_ins=ec.measure_name and sc.channel = ec.instantaneous_channel and sc.channel=rd01.sensor_channel and sc.remarks='瞬时值' and ec.flowmeter_caliber='流量' group by rd01.sensor_id,rd01.sensor_channel ) avg01, energy_classification02 ec, sensor_cfg sc where max01.r_sid=min01.r_sid and min01.r_sid=avg01.r_sid and max01.r_sid=sc.sensor_id and sc.channel = ec.instantaneous_channel and sc.channel= min01.r_channel and sc.channel= max01.r_channel and sc.channel=avg01.r_channel and sc.external_ins=ec.measure_name and sc.remarks='瞬时值' and ec.flowmeter_caliber='流量' ) rt_data, (select
Re: [GENERAL] postgres issue
Craig Ringer wrote: On 9/01/2010 10:53 PM, Amy Smith wrote: snip / How to use mount for differnt region. I do not understand this question. Can you explain what you mean a bit more? Probably wants tablespaces? \\||/ Rod -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] initdb has trouble finding the right ascii_and_mic.so
Hello pgsql-general, I'm having trouble initializing the database cluster. Here's how it does: netra ~ # (cd /var/opt/csw/postgresql/8.4/pgdata; truss -f -o /var/tmp/initdb.truss su postgres -c /opt/csw/lib/postgresql/8.4/bin/sparcv9/initdb -D /var/opt/csw/postgresql/8.4/pgdata -E utf-8) The files belonging to this database system will be owned by user postgres. This user must also own the server process. The database cluster will be initialized with locale en_US.UTF-8. The default text search configuration will be set to english. fixing permissions on existing directory /var/opt/csw/postgresql/8.4/pgdata ... ok creating subdirectories ... ok selecting default max_connections ... 100 selecting default shared_buffers ... 32MB creating configuration files ... ok creating template1 database in /var/opt/csw/postgresql/8.4/pgdata/base/1 ... ok initializing pg_authid ... ok initializing dependencies ... ok creating system views ... ok loading system objects' descriptions ... ok creating conversions ... FATAL: could not load library /opt/csw/lib/postgresql/8.4/lib/ascii_and_mic.so: ld.so.1: postgres: fatal: /opt/csw/lib/postgresql/8.4/lib/ascii_and_mic.so: wrong ELF class: ELFCLASS32 STATEMENT: CREATE OR REPLACE FUNCTION ascii_to_mic (INTEGER, INTEGER, CSTRING, INTERNAL, INTEGER) RETURNS VOID AS '$libdir/ascii_and_mic', 'ascii_to_mic' LANGUAGE C STRICT; child process exited with exit code 1 initdb: removing contents of data directory /var/opt/csw/postgresql/8.4/pgdata --- There are 2 sets of libraries installed. /opt/csw/lib/postgresql/8.4/lib contains 32-bit libraries. /opt/csw/lib/postgresql/8.4/lib/64 contains the 64-bit set. Here's what initdb does: 7584: stat(/opt/csw/lib/postgresql/8.4/lib/ascii_and_mic, 0x7FFFE610) Err#2 ENOENT 7584: stat(/opt/csw/lib/postgresql/8.4/lib/ascii_and_mic.so, 0x7FFFE610) = 0 7584: stat(/opt/csw/lib/postgresql/8.4/lib/ascii_and_mic.so, 0x7FFFE6C0) = 0 7584: stat(/opt/csw/lib/postgresql/8.4/lib/ascii_and_mic.so, 0x7FFFDD20) = 0 7584: resolvepath(/opt/csw/lib/postgresql/8.4/lib/ascii_and_mic.so, /opt/csw/lib/postgresql/8.4/lib/ascii_and_mic.so, 1023) = 48 7584: open(/opt/csw/lib/postgresql/8.4/lib/ascii_and_mic.so, O_RDONLY) = 37 If I understand correctly, the problem is that /opt/csw/lib/postgresql/8.4/bin/initdb looks for the libraries in the wrong place: /opt/csw/lib/postgresql/8.4/lib instead of /opt/csw/lib/postgresql/8.4/lib/64. How can I tell it to look there? Maciej -- 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] Optimistic locking with multiple rows
On Sun, 10 Jan 2010 16:01:57 +0800, Craig Ringer wrote: On 6/01/2010 10:53 PM, John T. Dow wrote: I posted this several days ago to pgsql-jdbc but have had no response. I am posting it here (with minor changes in the wording). I have developed some code that works, I'm just not sure I have the best solution. I have applications in which the user can create a read-only resultset with multiple rows. For example, customers who are 90 days in arrears might be brought up for review. The user might scroll through the rows reviewing the data, and then he might decide to update one of them. A second query is used to update that one row. At the time of the update, the current contents of that row is reread FOR UPDATE and compared against the original row. If they differ, someone else has altered the row after the resultset was created. The user is informed that another user has changed the row in question; he can then decide to accept the changes he has made or leave in place the changes made by the other user. In either case, that row in the original resultset has to be made to match the current contents in the table, because the user might scroll back and forth and revisit it. I am using refreshRow() to make that row current, but the problem is that refreshRow() can be extremely slow. I create the read-only, multiple row resultset (ie viewResultSet) like this: createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); viewResultSet = jdbcStmt.executeQuery(SELECT ... FROM ... WHERE select multiple rows); Scroll through the resultset to view rows as desired. When positioned at a row, can update that row. See below. Can you instead create an explicit, named updatable cursor? Then FETCH from it, and UPDATE ... WHERE CURRENT OF it? That way you won't have to do all this kludging with refreshing rowsets. -- Craig Ringer I have not used cursors, so you've inspired me to rtfm about them. So far I don't see how to implement optimistic locking with cursors. The postgres documentation says this: Without FOR UPDATE, a subsequent WHERE CURRENT OF command will have no effect if the row was changed since the cursor was created. I read that to mean that the cursor has to be declared FOR UPDATE, which means that the rows are locked and I don't have optimistic locking. John -- 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] initdb has trouble finding the right ascii_and_mic.so
Maciej (Matchek) Blizinski blizin...@google.com writes: There are 2 sets of libraries installed. /opt/csw/lib/postgresql/8.4/lib contains 32-bit libraries. /opt/csw/lib/postgresql/8.4/lib/64 contains the 64-bit set. You can't just make up some random layout for the libraries. Even if this could work, I don't see a lot of point to it. Why don't you set up two independent installation trees if you want to have both 32- and 64-bit postgres installed? 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] initdb has trouble finding the right ascii_and_mic.so
On Sun, Jan 10, 2010 at 4:05 PM, Tom Lane t...@sss.pgh.pa.us wrote: Maciej (Matchek) Blizinski blizin...@google.com writes: There are 2 sets of libraries installed. /opt/csw/lib/postgresql/8.4/lib contains 32-bit libraries. /opt/csw/lib/postgresql/8.4/lib/64 contains the 64-bit set. You can't just make up some random layout for the libraries. The layout is not exactly random. On Solaris: mac...@netra ~ $ ls -ld /usr/lib/32 lrwxrwxrwx 1 root root 1 Feb 14 2009 /usr/lib/32 - . mac...@netra ~ $ ls -ld /usr/lib/64 lrwxrwxrwx 1 root root 7 Feb 14 2009 /usr/lib/64 - sparcv9 And, by analogy: mac...@netra ~ $ ls -ld /opt/csw/lib/32 lrwxrwxrwx 1 root root 1 Sep 25 08:48 /opt/csw/lib/32 - . mac...@netra ~ $ ls -ld /opt/csw/lib/64 lrwxrwxrwx 1 root root 7 Sep 25 08:48 /opt/csw/lib/64 - sparcv9 The whole operating system is laid out this way. The issue of finding the right set of libraries is solved in the following way: mac...@netra ~ $ /usr/ccs/bin/dump -Lv /opt/csw/bin/sparcv8/gpg | gegrep 'R(UN|)PATH' [13]RUNPATH /opt/csw/lib/$ISALIST:/opt/csw/lib:/opt/csw/lib:/opt/csw/lib [14]RPATH /opt/csw/lib/$ISALIST:/opt/csw/lib:/opt/csw/lib:/opt/csw/lib mac...@netra ~ $ /usr/ccs/bin/dump -Lv /opt/csw/bin/sparcv9/gpg | gegrep 'R(UN|)PATH' [11]RUNPATH /opt/csw/lib/$ISALIST:/opt/csw/lib/64 [12]RPATH /opt/csw/lib/$ISALIST:/opt/csw/lib/64 $ISALIST expands to: sparcv9+vis sparcv9 sparcv8plus+vis sparcv8plus sparcv8 sparcv8-fsmuld sparcv7 sparc The binary checks each element of this list, substituting the $ISALIST token for the path. After the expansion, /opt/csw/lib/$ISALIST becomes /opt/csw/lib/sparcv9+vis, /opt/csw/lib/sparcv9, /opt/csw/lib/sparcv8plus+vis, etc. In the case of the initdb binary: mac...@netra ~ $ /usr/ccs/bin/dump -Lv /opt/csw/lib/postgresql/8.4/bin/sparcv8/initdb | gegrep 'R(UN|)PATH' [17]RUNPATH /opt/csw/lib/$ISALIST:/opt/csw/lib:/opt/csw/lib/postgresql/8.4/lib/$ISALIST:/opt/csw/lib/postgresql/8.4/lib:/opt/csw/lib:/opt/csw/lib/postgresql/8.4/lib [18]RPATH /opt/csw/lib/$ISALIST:/opt/csw/lib:/opt/csw/lib/postgresql/8.4/lib/$ISALIST:/opt/csw/lib/postgresql/8.4/lib:/opt/csw/lib:/opt/csw/lib/postgresql/8.4/lib mac...@netra ~ $ /usr/ccs/bin/dump -Lv /opt/csw/lib/postgresql/8.4/bin/sparcv9/initdb | gegrep 'R(UN|)PATH' [17]RUNPATH /opt/csw/lib/$ISALIST:/opt/csw/lib/64:/opt/csw/lib/postgresql/8.4/lib/$ISALIST:/opt/csw/lib/postgresql/8.4/lib/64:/opt/csw/lib/postgresql/8.4/lib [18]RPATH /opt/csw/lib/$ISALIST:/opt/csw/lib/64:/opt/csw/lib/postgresql/8.4/lib/$ISALIST:/opt/csw/lib/postgresql/8.4/lib/64:/opt/csw/lib/postgresql/8.4/lib The search path for the 64-bit binary is: /opt/csw/lib/$ISALIST /opt/csw/lib/64 /opt/csw/lib/postgresql/8.4/lib/$ISALIST /opt/csw/lib/postgresql/8.4/lib/64 /opt/csw/lib/postgresql/8.4/lib All the binaries have this RPATH set, so they have enough information to find the right libraries. Even if this could work, I don't see a lot of point to it. Why don't you set up two independent installation trees if you want to have both 32- and 64-bit postgres installed? That is an option, but I wanted to avoid separate installation trees in the first place. I'm working on replicating the Debian layout, to support installing multiple PostgreSQL versions at the same time. Currently, OpenCSW package[1] install PostgreSQL in /opt/csw/postgresql and there's no room for another version and/or for data migration. There are also issues with things like the PATH setting, so the PostgreSQL package doesn't work out of the box. I want to fix that. My options seem to be: - find a way to make initdb use the information from RPATH - ship 32-bit binaries only Maciej [1] http://www.opencsw.org/packages/postgresql -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 8.4.1 8.4.2 require map=... for ident in the pg_hba.conf file, will this be the new way?
Hello, In older PostgreSQL installations 8.4 I used the following pg_hba.conf syntax: local all pgsql ident PGSQL but 8.4.1 8.4.2 require the following syntax: local all pgsql ident map=PGSQL Will this be the new way for all future releases? Thanks in advance for your answer Regards Claudio -- Tel +41 21 67 17 111 Mob +41 79 34 72 100 clau...@yourshop.com Http://YourShop.com/ Come to me all who are weary and burdened and I will give you rest -- Jesus Christ -- 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] 8.4.1 8.4.2 require map=... for ident in the pg_hba.conf file, will this be the new way?
On tis, 2010-01-05 at 16:35 +0100, Claudio Eichenberger wrote: Hello, In older PostgreSQL installations 8.4 I used the following pg_hba.conf syntax: local all pgsql ident PGSQL but 8.4.1 8.4.2 require the following syntax: local all pgsql ident map=PGSQL Will this be the new way for all future releases? Yes, at least until someone has an idea to change it again. ;-) -- 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] Return Single Row Result After Inserting (Stored Procedure)
Thanks!
However, we prefer to stick with plpgsql, as rest of our functions are in that
language. We need some consistency.
I try to modify my previous stored procedure to.
CREATE OR REPLACE FUNCTION create_lot(text, text, text)
RETURNS lot AS
$BODY$DECLARE
configurationFile ALIAS FOR $1;
operatorName ALIAS FOR $2;
machineName ALIAS FOR $3;
BEGIN
INSERT INTO lot(configuration_file, operator_name, machine_name)
VALUES(configurationFile, operatorName, machineName) RETURNING *;
END;$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
ALTER FUNCTION create_lot(text, text, text) OWNER TO postgres;
However, we get the following error.
SemiconductorInspection=# SELECT * FROM create_lot('a','b','3');
ERROR: query has no destination for result data
CONTEXT: PL/pgSQL function create_lot line 9 at SQL statement
Any suggestion? Thanks!
Thanks and Regards
Yan Cheng CHEOK
--
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] Return Single Row Result After Inserting (Stored Procedure)
On Sunday 10 January 2010 5:49:38 pm Yan Cheng Cheok wrote:
Thanks!
However, we prefer to stick with plpgsql, as rest of our functions are in
that language. We need some consistency.
I try to modify my previous stored procedure to.
CREATE OR REPLACE FUNCTION create_lot(text, text, text)
RETURNS lot AS
$BODY$DECLARE
configurationFile ALIAS FOR $1;
operatorName ALIAS FOR $2;
machineName ALIAS FOR $3;
BEGIN
INSERT INTO lot(configuration_file, operator_name, machine_name)
VALUES(configurationFile, operatorName, machineName) RETURNING *;
END;$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
ALTER FUNCTION create_lot(text, text, text) OWNER TO postgres;
However, we get the following error.
SemiconductorInspection=# SELECT * FROM create_lot('a','b','3');
ERROR: query has no destination for result data
CONTEXT: PL/pgSQL function create_lot line 9 at SQL statement
Any suggestion? Thanks!
Thanks and Regards
Yan Cheng CHEOK
See here;
http://www.postgresql.org/docs/8.4/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW
--
Adrian Klaver
adrian.kla...@gmail.com
--
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] Return Single Row Result After Inserting (Stored Procedure)
Thanks a lot. I solved my problem by using this.
CREATE OR REPLACE FUNCTION create_lot(text, text, text)
RETURNS lot AS
$BODY$DECLARE
configurationFile ALIAS FOR $1;
operatorName ALIAS FOR $2;
machineName ALIAS FOR $3;
_lot lot;
BEGIN
INSERT INTO lot(configuration_file, operator_name, machine_name)
VALUES(configurationFile, operatorName, machineName) RETURNING * INTO _lot;
return _lot;
END;$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
ALTER FUNCTION create_lot(text, text, text) OWNER TO postgres;
Thanks and Regards
Yan Cheng CHEOK
--- On Mon, 1/11/10, Adrian Klaver adrian.kla...@gmail..com wrote:
From: Adrian Klaver adrian.kla...@gmail.com
Subject: Re: [GENERAL] Return Single Row Result After Inserting (Stored
Procedure)
To: pgsql-general@postgresql.org
Cc: Yan Cheng Cheok ycch...@yahoo.com, t...@sss.pgh.pa.us
Date: Monday, January 11, 2010, 11:03 AM
On Sunday 10 January 2010 5:49:38 pm
Yan Cheng Cheok wrote:
Thanks!
However, we prefer to stick with plpgsql, as rest of
our functions are in
that language. We need some consistency.
I try to modify my previous stored procedure to.
CREATE OR REPLACE FUNCTION create_lot(text, text,
text)
RETURNS lot AS
$BODY$DECLARE
configurationFile ALIAS FOR $1;
operatorName ALIAS FOR $2;
machineName ALIAS FOR $3;
BEGIN
INSERT INTO
lot(configuration_file, operator_name, machine_name)
VALUES(configurationFile,
operatorName, machineName) RETURNING *;
END;$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
ALTER FUNCTION create_lot(text, text, text)
OWNER TO postgres;
However, we get the following error.
SemiconductorInspection=# SELECT * FROM
create_lot('a','b','3');
ERROR: query has no destination for result data
CONTEXT: PL/pgSQL function create_lot line 9
at SQL statement
Any suggestion? Thanks!
Thanks and Regards
Yan Cheng CHEOK
See here;
http://www.postgresql.org/docs/8.4/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW
--
Adrian Klaver
adrian.kla...@gmail.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Get Unix timestamp from SQL timestamp through libpq
I know I can convert SQL timestamp to unix timestamp, using the following way. SELECT extract(epoch FROM now()); Now, I have a stored procedure function, which will directly return a table row to the caller. One of the row field is timestamp type. In my application, I am using libpq. I wish to use libpq functions (or any c/c++ function), to convert 2010-01-11 13:10:55.283 into unix timestamp. Off course, I can create another stored procedure named SQLTimestamp2UnixTimestamp SELECT extract(epoch FROM $1); But I just wish to accomplish this task with a single c/c++ function call, without involving stored procedure. Any suggestion? Thanks! Thanks and Regards Yan Cheng CHEOK -- 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] pgsql2shp usage
On Saturday 09 January 2010 19:03:59 Randall Thompson wrote: The Attribute Tables for a shape file are stored in .DBF format which has a restriction that field names are only ten characters long, so the warnings are simply informing you that those field names that are longer than 10 characters are being truncated. The projection definition indicates that the output spatial reference system (900913) is a variation of the Mercator projection which is used by Google Maps, Bing Maps, and other web map services. So, these are not lat/long coordinates. Randall Thompson Castle Rock GIS Many thanks to you for bothering to reply ! As you said that they are not the latitude/longitude coordinates, please guide me, if you can, that where should I get the information regarding the projections w.r.t map of india stored in the shape file ? -- Regards, Anisha Kaul - Hi-Tech Gears Limited, Gurgaon, India -- 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] Get Unix timestamp from SQL timestamp through libpq
Give a try to: man 2 time man 3 ctime 2010/1/11 Yan Cheng Cheok ycch...@yahoo.com: I know I can convert SQL timestamp to unix timestamp, using the following way. SELECT extract(epoch FROM now()); Now, I have a stored procedure function, which will directly return a table row to the caller. One of the row field is timestamp type. In my application, I am using libpq. I wish to use libpq functions (or any c/c++ function), to convert 2010-01-11 13:10:55.283 into unix timestamp. Off course, I can create another stored procedure named SQLTimestamp2UnixTimestamp SELECT extract(epoch FROM $1); But I just wish to accomplish this task with a single c/c++ function call, without involving stored procedure. Any suggestion? Thanks! Thanks and Regards Yan Cheng CHEOK -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Vincenzo Romano NotOrAnd Information Technologies cel. +39 339 8083886 | gtalk. vincenzo.rom...@notorand.it fix. +39 0823 454163 | skype. notorand.it fax. +39 02 700506964 | msn. notorand.it NON QVIETIS MARIBVS NAVTA PERITVS -- 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] Get Unix timestamp from SQL timestamp through libpq
I try already. Neither of them can accept string parameter, and convert them to unit timestamp (long). Thanks and Regards Yan Cheng CHEOK --- On Mon, 1/11/10, Vincenzo Romano vincenzo.rom...@notorand.it wrote: From: Vincenzo Romano vincenzo.rom...@notorand.it Subject: Re: Get Unix timestamp from SQL timestamp through libpq To: Yan Cheng Cheok ycch...@yahoo.com Cc: pgsql-general@postgresql.org Date: Monday, January 11, 2010, 2:58 PM Give a try to: man 2 time man 3 ctime 2010/1/11 Yan Cheng Cheok ycch...@yahoo.com: I know I can convert SQL timestamp to unix timestamp, using the following way. SELECT extract(epoch FROM now()); Now, I have a stored procedure function, which will directly return a table row to the caller. One of the row field is timestamp type. In my application, I am using libpq. I wish to use libpq functions (or any c/c++ function), to convert 2010-01-11 13:10:55.283 into unix timestamp. Off course, I can create another stored procedure named SQLTimestamp2UnixTimestamp SELECT extract(epoch FROM $1); But I just wish to accomplish this task with a single c/c++ function call, without involving stored procedure. Any suggestion? Thanks! Thanks and Regards Yan Cheng CHEOK -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Vincenzo Romano NotOrAnd Information Technologies cel. +39 339 8083886 | gtalk. vincenzo.rom...@notorand.it fix. +39 0823 454163 | skype. notorand.it fax. +39 02 700506964 | msn. notorand..it NON QVIETIS MARIBVS NAVTA PERITVS -- 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] Get Unix timestamp from SQL timestamp through libpq
man 3 strftime 2010/1/11 Yan Cheng Cheok ycch...@yahoo.com: I try already. Neither of them can accept string parameter, and convert them to unit timestamp (long). Thanks and Regards Yan Cheng CHEOK --- On Mon, 1/11/10, Vincenzo Romano vincenzo.rom...@notorand.it wrote: From: Vincenzo Romano vincenzo.rom...@notorand.it Subject: Re: Get Unix timestamp from SQL timestamp through libpq To: Yan Cheng Cheok ycch...@yahoo.com Cc: pgsql-general@postgresql.org Date: Monday, January 11, 2010, 2:58 PM Give a try to: man 2 time man 3 ctime 2010/1/11 Yan Cheng Cheok ycch...@yahoo.com: I know I can convert SQL timestamp to unix timestamp, using the following way. SELECT extract(epoch FROM now()); Now, I have a stored procedure function, which will directly return a table row to the caller. One of the row field is timestamp type. In my application, I am using libpq. I wish to use libpq functions (or any c/c++ function), to convert 2010-01-11 13:10:55.283 into unix timestamp. Off course, I can create another stored procedure named SQLTimestamp2UnixTimestamp SELECT extract(epoch FROM $1); But I just wish to accomplish this task with a single c/c++ function call, without involving stored procedure. Any suggestion? Thanks! Thanks and Regards Yan Cheng CHEOK -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Vincenzo Romano NotOrAnd Information Technologies cel. +39 339 8083886 | gtalk. vincenzo.rom...@notorand.it fix. +39 0823 454163 | skype. notorand.it fax. +39 02 700506964 | msn. notorand..it NON QVIETIS MARIBVS NAVTA PERITVS -- Vincenzo Romano NotOrAnd Information Technologies cel. +39 339 8083886 | gtalk. vincenzo.rom...@notorand.it fix. +39 0823 454163 | skype. notorand.it fax. +39 02 700506964 | msn. notorand.it NON QVIETIS MARIBVS NAVTA PERITVS -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
