Re: [GENERAL] Inserting data from one database to another using stored functions
On Thu, Jan 6, 2011 at 6:56 PM, Benjie Buluran
benjie.bulu...@igentechnologies.com wrote:
Hi pgSQL peeps!
I’m stumped on this question for over 3 days now.
I need to run a stored function in Database A (“sf DBa”) which calls a
stored function in Database B (“sf DBb”).
BEGIN
PERFORM dblink_connect('dbname=testdb
port=5432 user=postgres password=123456');
PERFORM dblink_exec('SELECT
sp_insert_detailtable('|| pActivityId ||', '|| pserialnumber ||')');
PERFORM dblink_disconnect();
END;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
Here’s “sf DBb”:
CREATE OR REPLACE FUNCTION sp_insert_detailtable(pactivityid integer,
pserialnumber character varying)
RETURNS void AS
$BODY$
BEGIN
INSERT INTO DETAILTABLE(LogID, LogDetailSeq)
VALUES(pactivityid, pserialnumber);
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
I’m using the DEBUG function in pgAdmin, and I keep getting the “*statement
returning results not allowed*” error in *PERFORM dblink_exec('SELECT
sp_insert_detailtable('|| pActivityId ||', '|| pserialnumber ||')');* in
this line.
Your help is highly appreciated!
Thanks and Best Regards,
Benjie
dblink_exec is only for commands which return no result. Try replacing that
line with the following and see if it helps:
PERFORM dblink('SELECT sp_insert_detailtable('|| pActivityId ||', '||
pserialnumber ||')');
-Eric
Re: [GENERAL] Inserting data from one database to another using stored functions
Hi Eric,
I have tested your reply and it works J!
Thank you for your help!
By the way, here's what I did with the calling stored function (sf DBa):
CREATE OR REPLACE FUNCTION sp_update_serialnumber(pserialnumber character
varying, pActivityId integer)
RETURNS void AS
$BODY$
DECLARE
r record;
BEGIN
UPDATE TABLESSERIALNUM SET SerialNumber = pSerialNumber
WHERE ActivityID = pActivityId ;
BEGIN
PERFORM * FROM dblink('dbname=testdb
port=5432 user=postgres password=123456', 'SELECT * FROM
sp_insert_detailtable('|| pActivityId ||', '|| pserialnumber ||')') as
r(result character varying(50));
END;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
Regards,
Benjie
From: Eric McKeeth [mailto:eldi...@gmail.com]
Sent: Friday, January 07, 2011 4:03 PM
To: Benjie Buluran
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Inserting data from one database to another using
stored functions
On Thu, Jan 6, 2011 at 6:56 PM, Benjie Buluran
benjie.bulu...@igentechnologies.com wrote:
Hi pgSQL peeps!
I'm stumped on this question for over 3 days now.
I need to run a stored function in Database A (sf DBa) which calls a
stored function in Database B (sf DBb).
BEGIN
PERFORM dblink_connect('dbname=testdb
port=5432 user=postgres password=123456');
PERFORM dblink_exec('SELECT
sp_insert_detailtable('|| pActivityId ||', '|| pserialnumber ||')');
PERFORM dblink_disconnect();
END;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
Here's sf DBb:
CREATE OR REPLACE FUNCTION sp_insert_detailtable(pactivityid integer,
pserialnumber character varying)
RETURNS void AS
$BODY$
BEGIN
INSERT INTO DETAILTABLE(LogID, LogDetailSeq)
VALUES(pactivityid, pserialnumber);
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
I'm using the DEBUG function in pgAdmin, and I keep getting the statement
returning results not allowed error in PERFORM dblink_exec('SELECT
sp_insert_detailtable('|| pActivityId ||', '|| pserialnumber ||')'); in this
line.
Your help is highly appreciated!
Thanks and Best Regards,
Benjie
dblink_exec is only for commands which return no result. Try replacing that
line with the following and see if it helps:
PERFORM dblink('SELECT sp_insert_detailtable('|| pActivityId ||', '||
pserialnumber ||')');
-Eric
_
No virus found in this message.
Checked by AVG - www.avg.com
Version: 10.0.1191 / Virus Database: 1435/3364 - Release Date: 01/06/11
[GENERAL] Query to find sum of grouped counts from 2 tables
I have 2 tables containing the data for same items: STORE1 - Id typeitems - 1 FOOD10 2 FOOD15 3 SOAP20 STORE2 - Id typeitems - 1 FOOD15 3 SOAP10 4 PAPER 25 5 SOAP12 What I am looking for is one single query that would return me TYPE-wise total number of items from both the tables. UNION does not help me. I want the result as: Typecount --- FOOD40 //10+15+15 SOAP42 //20+10+12 PAPER 25 Thanks in advance, -Satish -- 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] Query to find sum of grouped counts from 2 tables
Satish Burnwal (sburnwal) wrote on 07.01.2011 11:15: I have 2 tables containing the data for same items: STORE1 - Id typeitems - 1 FOOD10 2 FOOD15 3 SOAP20 STORE2 - Id typeitems - 1 FOOD15 3 SOAP10 4 PAPER 25 5 SOAP12 What I am looking for is one single query that would return me TYPE-wise total number of items from both the tables. UNION does not help me. I want the result as: Hmm, I don't see why UNION shouldn't work: SELECT type, sum(items) as count FROM ( SELECT type, items FROM store1 UNION ALL SELECT type, items FROM store2 ) t GROUP BY type -- 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] Inserting data from one database to another using stored functions
On 07/01/11 01:56, Benjie Buluran wrote:
Hi pgSQL peeps!
I’m stumped on this question for over 3 days now.
PERFORM dblink_exec('SELECT sp_insert_detailtable('|| pActivityId ||',
'|| pserialnumber ||')');
I’m using the DEBUG function in pgAdmin, and I keep getting the
“*statement returning results not allowed*” error in /PERFORM
dblink_exec('SELECT sp_insert_detailtable('|| pActivityId ||', '||
pserialnumber ||')');/ in this line.
A quick look at the docs for dblink_exec say dblink_exec executes a
command (that is, any SQL statement that doesn't return rows). A SELECT
statement returns rows. Zero rows are still rows. What happens if you
just use dblink(...)?
http://www.postgresql.org/docs/9.0/static/contrib-dblink-exec.html
http://www.postgresql.org/docs/9.0/static/contrib-dblink.html
--
Richard Huxton
Archonet Ltd
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Backup and restore sequences
PostgreSQL 9.0.1/pgAdminIII 1.12.1
I want to copy selected tables from one database to another and maintain the
sequences which I originally setup with:
CREATE SEQUENCE venues_id_seq START WITH 1122;
ALTER TABLE venues ALTER COLUMN id SET DEFAULT nextval('venues_id_seq');
... along with their current values, which have been augmented since the
database was setup. When I backup via pgAdminIII the sequences are not even
included. I also can't find anything in:
man pg_dump
... which specifies sequences.
gvim
--
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] Backup and restore sequences
On Friday 07 January 2011 7:46:31 am gvim wrote:
PostgreSQL 9.0.1/pgAdminIII 1.12.1
I want to copy selected tables from one database to another and maintain
the sequences which I originally setup with:
CREATE SEQUENCE venues_id_seq START WITH 1122;
ALTER TABLE venues ALTER COLUMN id SET DEFAULT nextval('venues_id_seq');
... along with their current values, which have been augmented since the
database was setup. When I backup via pgAdminIII the sequences are not even
included. I also can't find anything in:
man pg_dump
... which specifies sequences.
gvim
Details below. When you dump a specific table using -t it will not
automatically dump dependent objects. -t can be used to dump a sequence because
they are just a special type of table.
From:
http://www.postgresql.org/docs/9.0/interactive/app-pgdump.html
-t table
--table=table
Dump only tables (or views or sequences) matching table. Multiple tables
can
be selected by writing multiple -t switches. Also, the table parameter is
interpreted as a pattern according to the same rules used by psql's \d commands
(see Patterns), so multiple tables can also be selected by writing wildcard
characters in the pattern. When using wildcards, be careful to quote the
pattern if needed to prevent the shell from expanding the wildcards.
The -n and -N switches have no effect when -t is used, because tables
selected by -t will be dumped regardless of those switches, and non-table
objects will not be dumped.
Note: When -t is specified, pg_dump makes no attempt to dump any other
database objects that the selected table(s) might depend upon. Therefore, there
is no guarantee that the results of a specific-table dump can be successfully
restored by themselves into a clean database.
Note: The behavior of the -t switch is not entirely upward compatible
with pre-8.2 PostgreSQL versions. Formerly, writing -t tab would dump all
tables named tab, but now it just dumps whichever one is visible in your
default search path. To get the old behavior you can write -t '*.tab'. Also,
you must write something like -t sch.tab to select a table in a particular
schema, rather than the old locution of -n sch -t tab.
--
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] Backup and restore sequences
On January 7, 2011, gvim gvi...@gmail.com wrote:
PostgreSQL 9.0.1/pgAdminIII 1.12.1
I want to copy selected tables from one database to another and maintain
the sequences which I originally setup with:
CREATE SEQUENCE venues_id_seq START WITH 1122;
ALTER TABLE venues ALTER COLUMN id SET DEFAULT nextval('venues_id_seq');
... along with their current values, which have been augmented since the
database was setup. When I backup via pgAdminIII the sequences are not
even included. I also can't find anything in:
man pg_dump
... which specifies sequences.
gvim
--table=table
Dump only tables (or views or sequences) matching table.
Multiple tables can be selected
by writing multiple -t switches. Also, the table parameter
is interpreted as a pattern
according to the same rules used by psql’s \d commands (see
Patterns [psql(1)]), so multi-
ple tables can also be selected by writing wildcard
characters in the pattern. When using
wildcards, be careful to quote the pattern if needed to
prevent the shell from expanding
the wildcards.
--
A hybrid Escalade is missing the point much in the same way that having a
diet soda with your extra large pepperoni pizza is missing the point.
[GENERAL] OOO and postgres
Hi guys
I am using the postgres driver for OOO and just ran into the following
error:
Error code: 1
pq_driver: [PGRES_FATAL_ERROR]ERROR: array value must start with { or
dimension information
LINE 1: ...O public.Bladetypes ( ID,type) VALUES ( '1','Knife')
^
(caused by statement 'INSERT INTO public.Bladetypes ( ID,type)
VALUES ( '1','Knife')')
the table looks like this:
CREATE TABLE Bladetypes
(
ID integer NOT NULL,
type character varying[] NOT NULL,
CONSTRAINT Bladetypes_pkey PRIMARY KEY (ID)
)
ALTER TABLE Bladetypes ADD COLUMN ID integer;
ALTER TABLE Bladetypes ALTER COLUMN ID SET NOT NULL;
ALTER TABLE Bladetypes ADD COLUMN type character varying[];
ALTER TABLE Bladetypes ALTER COLUMN type SET NOT NULL;
is this for this list?
Thanks
Bernhard
--
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] OOO and postgres
On 01/07/2011 09:40 AM, Bernhard Rohrer wrote: Hi guys I am using the postgres driver for OOO and just ran into the following error: Error code: 1 If you are referring to OpenOffice and the native SDBC driver then from the docs: http://dba.openoffice.org/drivers/postgresql/index.html#features data types like clobs, blobs and arrays are not yet supported. The whole datatype handling for non-standard datatypes is crippled currently, here needs to be developed a concept first. -- 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] OOO and postgres
On Fri, 7 Jan 2011, Bernhard Rohrer wrote: CREATE TABLE Bladetypes ( ID integer NOT NULL, type character varying[] NOT NULL, CONSTRAINT Bladetypes_pkey PRIMARY KEY (ID) ) ALTER TABLE Bladetypes ADD COLUMN ID integer; ALTER TABLE Bladetypes ALTER COLUMN ID SET NOT NULL; ALTER TABLE Bladetypes ADD COLUMN type character varying[]; ALTER TABLE Bladetypes ALTER COLUMN type SET NOT NULL; Don't use double quotes in your create table stanza. You can use them on the table name with alter table and insert into. The data type is VARCHAR(), not character varying[]. Why are you altering the table to be exactly how you defined it? Use single quotes to define text strings in your values statements. Perhaps you'll find value in reading a book on SQL. Rick van der Lans and Joe Celko both write outstanding books on the language and its use. Rich -- 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] OOO and postgres
Per the error message, you need to enclose array values in braces. For
example, something like:
INSERT into Bladetypes (ID, type), values ('1', '{Knife}');
-Original Message-
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Bernhard Rohrer
Sent: Friday, January 07, 2011 9:41 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] OOO and postgres
Hi guys
I am using the postgres driver for OOO and just ran into the following
error:
Error code: 1
pq_driver: [PGRES_FATAL_ERROR]ERROR: array value must start with { or
dimension information
LINE 1: ...O public.Bladetypes ( ID,type) VALUES ( '1','Knife')
^
(caused by statement 'INSERT INTO public.Bladetypes ( ID,type)
VALUES ( '1','Knife')')
the table looks like this:
CREATE TABLE Bladetypes
(
ID integer NOT NULL,
type character varying[] NOT NULL,
CONSTRAINT Bladetypes_pkey PRIMARY KEY (ID)
)
ALTER TABLE Bladetypes ADD COLUMN ID integer;
ALTER TABLE Bladetypes ALTER COLUMN ID SET NOT NULL;
ALTER TABLE Bladetypes ADD COLUMN type character varying[];
ALTER TABLE Bladetypes ALTER COLUMN type SET NOT NULL;
is this for this list?
Thanks
Bernhard
--
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
Re: [GENERAL] OOO and postgres
Rich Shepard wrote on 07.01.2011 18:56: The data type is VARCHAR(), not character varying[]. character varying is a synonym for varchar, so the definition character varying[] is valid. It defines an array of varchar and is equivalent to varchar[] But I doubt that this is what the OP meant ;) Regards Thomas -- 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] OOO and postgres
What I am not getting is - as far as I can see none of the fields _is_
an array! So why would this error message appear?
On 07/01/11 17:40, Bernhard Rohrer wrote:
Hi guys
I am using the postgres driver for OOO and just ran into the following
error:
Error code: 1
pq_driver: [PGRES_FATAL_ERROR]ERROR: array value must start with {
or dimension information
LINE 1: ...O public.Bladetypes ( ID,type) VALUES ( '1','Knife')
^
(caused by statement 'INSERT INTO public.Bladetypes (
ID,type) VALUES ( '1','Knife')')
the table looks like this:
CREATE TABLE Bladetypes
(
ID integer NOT NULL,
type character varying[] NOT NULL,
CONSTRAINT Bladetypes_pkey PRIMARY KEY (ID)
)
ALTER TABLE Bladetypes ADD COLUMN ID integer;
ALTER TABLE Bladetypes ALTER COLUMN ID SET NOT NULL;
ALTER TABLE Bladetypes ADD COLUMN type character varying[];
ALTER TABLE Bladetypes ALTER COLUMN type SET NOT NULL;
is this for this list?
Thanks
Bernhard
--
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] OOO and postgres
On 01/07/2011 10:16 AM, Bernhard Rohrer wrote:
What I am not getting is - as far as I can see none of the fields _is_
an array! So why would this error message appear?ion
The type field is an array type because of the square brackets after
the data type.
type character varying[] NOT NULL
The INSERT is not inserting the VALUES for an array correctly. To get
back to my original question, what driver are you using? If it is the
native SDBC one it does not know about arrays and is probably the cause
of the problem.
On 07/01/11 17:40, Bernhard Rohrer wrote:
Hi guys
I am using the postgres driver for OOO and just ran into the following
error:
Error code: 1
pq_driver: [PGRES_FATAL_ERROR]ERROR: array value must start with {
or dimension information
LINE 1: ...O public.Bladetypes ( ID,type) VALUES ( '1','Knife')
^
(caused by statement 'INSERT INTO public.Bladetypes ( ID,type)
VALUES ( '1','Knife')')
the table looks like this:
CREATE TABLE Bladetypes
(
ID integer NOT NULL,
type character varying[] NOT NULL,
CONSTRAINT Bladetypes_pkey PRIMARY KEY (ID)
Thanks
Bernhard
--
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] Backup and restore sequences
On 01/07/2011 08:26 AM, gvim wrote: On 07/01/2011 15:58, Adrian Klaver wrote: Details below. When you dump a specific table using -t it will not automatically dump dependent objects. -t can be used to dump a sequence because they are just a special type of table. From: http://www.postgresql.org/docs/9.0/interactive/app-pgdump.html -t table --table=table Thanks. I just found this and it works great but wish pgAdmin had something similar. gvim I broke down and installed pgAdmin. You can do what you want by finding the sequence in the object browser on the left and right clicking and selecting CREATE script. -- 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
[GENERAL] Remote Connection
Hi I am attempting to connect from two local interfaces to a remote database. With one interface (SharpMap developed in C#) I have no problems. With the other interface (Delphi) I have no problem connecting in design mode. However when I compile Delphi it just hangs, until timeout, without opening. The postgresql log follows. Can someone please interpret it for me? Bob 2011-01-07 09:03:55 PSTERROR: unrecognized configuration parameter ssl_renegotiation_limit 2011-01-07 09:03:55 PSTSTATEMENT: SET ssl_renegotiation_limit=0 2011-01-07 09:04:08 PSTLOG: could not receive data from client: No connection could be made because the target machine actively refused it. 2011-01-07 09:04:08 PSTLOG: unexpected EOF on client connection 2011-01-07 09:22:58 PSTLOG: could not receive data from client: No connection could be made because the target machine actively refused it. 2011-01-07 09:22:58 PSTLOG: unexpected EOF on client connection
[GENERAL] Realtime Query Dashboard Results
Just looking for understanding/ideas. I assume when people use dashboards they are not being queried every second for updating but maybe every minute? Are there any tools that work good on top of postgres? (I see in the stock market (though I am looking at/for production data) they seem to use tools that frequently update their dashboards. What is a realistic timeframe to expect query updates for a dashboard?
Re: [GENERAL] Realtime Query Dashboard Results
On 1/7/2011 1:52 PM, THOMPSON, JARED (ATTBAPCO) wrote: Just looking for understanding/ideas. I assume when people use dashboards they are not being queried every second for updating but maybe every minute? Are there any tools that work good on top of postgres? (I see in the stock market (though I am looking at/for production data) they seem to use tools that frequently update their dashboards. What is a realistic timeframe to expect query updates for a dashboard? I think it would depend on how busy the box is, and how expensive getting updates are. -Andy -- 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] Realtime Query Dashboard Results
On 7 Jan 2011, at 22:02, THOMPSON, JARED (ATTBAPCO) jt0...@att.com wrote: I assume when people use dashboards they are not being queried every second for updating but maybe every minute? Are there any tools that work good on top of postgres? (I see in the stock market (though I am looking at/for production data) they seem to use tools that frequently update their dashboards. What is a realistic timeframe to expect query updates for a dashboard? Having written a dashboard on top of PostgreSQL (screenshot at http://blog.agilebase.co.uk/2010/03/31/dashboard-preview/ ), I can at least state my decisions: Charts are updated once a day overnight, or cached whenever someone looks at them in the system underlying the dashboard, so they are at most one day old. A chart is also updated when a user clicks on it to drill down to the data. Of course what you decide depends on what the business use case is and what demands there are on the system. In my cases so far the slowest charts take 1 or 2 seconds to generate by SQL so if necessary, each could be loaded in in real time over AJAX, though that hasn't been needed yet. Regards Oliver Kohll
[GENERAL] cast question: max double precision text double precision fails with out or range error
postgres=# select (((1.7976931348623157081e+308)::double precision)::text)::double precision; ERROR: 1.79769313486232e+308 is out of range for type double precision I can't think of too many practical use cases here, but I'm working on a pg driver and in my float data decoder functional tests, I ran into some errors that I eventually traced back to this behavior. Essentially, postgres seems to cast the max normal double (i.e., the bits of ~(1ULL52 | 1ULL63)) to text in such a manner that it's rounded up, and the reverse cast, text-to-double-precision, does not recognize it as being in range. Is this just a case of don't do that? Curiously, pg_dump seems to print doubles with more precision (in both COPY and INSERT modes), avoiding this issue. Of course I'm not expecting perfect precision in round-tripping doubles like this (this is always dicey with IEEE floating point anyway), but failing outright is a little ugly. Any thoughts? Version is PostgreSQL 8.4.6 on i486-pc-linux-gnu, compiled by GCC gcc-4.4.real (Ubuntu 4.4.3-4ubuntu5) 4.4.3, 32-bit. Thanks, Maciek Sakrejda -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
