[SQL] how to update 400 000 register not at the same time?
I Have um very big table with primary key and all i nead. When i update same register from this table it comes sj=low. Can i Update all my 400 000 register not at the same time? By steps? Thanks -- John Evan Dizaro Software - Fone: (41) -0303 Fone: (41) 9243-3240 Rua: Alferes Poli Curitiba - PR - Brasil
Re: [SQL] wired behaviour
Shane Ambler wrote: Lutz Steinborn wrote: Jepp, thats it. I've supposed this but can't believe it. So NULL is something out of this dimension :-) NULL refers to an unknown value - it cannot be said to equal or not equal anything other than NULL ... Not exactly. Null does not equal null. The "translation" being does some-unknown-value equal some-unknown-value? Answer: unknown. If you want to determine if something is null you must use "is null". select null = null; null select null is null; true If you want to treat nulls as a defined known value, use the coalesce function: select coalesce(my_column, 'a null value'); will return the string 'a null value' whenever my_column is null. Cheers, Steve -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] [HACKERS] Reg: Nested query
Hi, It's poor manners to crosspost questions to such a huge number of lists, particularly when there is one among them that perfectly suits your question. Vignesh Raaj escribió: > Can anyone please help in getting a solution... > I have two table, 1 contains all details of a person with person id as > primary key. > the other table has person id and parent id both referring to person table > person id. > My query is i have to get all the parents of a given person name. > Please help me in finding a query solution... You can't do it short of writing a function that recurses, or using such tricks as contrib/ltree. In the upcoming 8.4 version you will be able to write queries with the WITH RECURSIVE construct to handle this directly in SQL. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] JOIN results of refcursor functions
Alvaro Herrera wrote: Milan Oparnica escribió: I've searched documentation (8.3) and didn't find a way to use OUT variables in same manner as SETOF (RETURN NEXT doesn't create a record type result). Can you please give an example of how to return select fld1, fld2 from table through OUT variables so the caller gets records ? create function foo (a int, out b int, out c text) returns setof record language plpgsql as $$ begin b = 2 * a; c = 'dos por a'; return next; I understand this example, but couldn't figure how to do the same thing with query results. Please help me build a function foo(insklid int, out sklid int, out elid int) returns setof record that will return result of select sklid, elid form skladkol where skladkol is a table CREATE TABLE skadkol (sklid int, elid int) I know this should be simple, but all examples I could find about OUT parameters use x:=something which is simple but doesn't help. I've tried: CREATE FUNCTION foo(insklid int, out sklid int, out elid INT) AS $$ BEGIN RETURN QUERY SELECT sklid,elid form skladkol where sklid2=insklid; END; $$ LANGUAGE plpgsql; but i get "cannot use RETURN QUERY in a non-SETOF function at or near "QUERY" Then I've tried: CREATE FUNCTION foo(insklid int, out sklid int, out elid INT) RETURNS SETOF record AS $$ BEGIN RETURN QUERY SELECT sklid,elid FROM skladkol; RETURN; END; $$ LANGUAGE plpgsql; but it returns 5498 rows (which is exact number of rows in that table) but of NULL values. WHAT AM I DOING WRONG ? :((( I'm using EMS Manager 2007 Lite for PostgreSQL. That shouldn't be a problem, right ? Regards, Milan Oparnica -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] DIVISION with Group By
Hai all,
I am new to SQL coding.
I need some help in writing SQL
In my task i have to calculate division of two sub queries.
each sub query has its own group by clause.
Here with i have Paste my table information and my SQL.
/* tdos table */
CREATE TABLE tdos
(
dosid integer NOT NULL DEFAULT nextval('dos_sequence'::regclass),
dcode character varying(20) NOT NULL,
count integer NOT NULL,
dosresult integer NOT NULL,
standardcode character varying(12) NOT NULL,
dosnakbn integer NOT NULL,
dosintrsv1 integer,
dosintrsv2 integer,
dosdatersv1 date,
dosdatersv2 date,
dostxtrsv1 text,
dostxtrsv2 text,
CONSTRAINT tdos_pkey PRIMARY KEY (dosid),
CONSTRAINT tdos_mstandard_fkey FOREIGN KEY (standardcode)
REFERENCES mstandard (standardcode) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT tdos_tdlr_fkey FOREIGN KEY (dlrcode)
REFERENCES tdlr (dlrcode) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (OIDS=FALSE);
ALTER TABLE tdos OWNER TO postgres;
/*mrea table*/
CREATE TABLE mrea
(
arcode character varying(20) NOT NULL,
aeregistdate date NOT NULL,
arupdatedate date NOT NULL,
arstartdate date,
arenddate date,
areaintrsv1 integer,
areaintrsv2 integer,
areadatersv1 date,
areadatersv2 date,
areatxtrsv1 text,
areatxtrsv2 text,
CONSTRAINT mrea_pkey PRIMARY KEY (arcode)
)
WITH (OIDS=FALSE);
ALTER TABLE mrea OWNER TO postgres;
/*mscn table*/
CREATE TABLE mscn
(
scncode character varying(20) NOT NULL,
arcode character varying(20) NOT NULL,
oldscncode character varying(20) NOT NULL,
dtkbn integer NOT NULL,
inputfilepath text NOT NULL,
outputfilepath text NOT NULL,
backupfilepath text NOT NULL,
nscstartdate date,
nscenddate date,
nscregistdate date NOT NULL,
nscupdatedate date NOT NULL,
nscintrsv1 integer,
nscintrsv2 integer,
nscdatersv1 date,
nscdatersv2 date,
nsctxtrsv1 text,
nsctxtrsv2 text,
CONSTRAINT mscn_pkey PRIMARY KEY (scncode),
CONSTRAINT mkey_fkey FOREIGN KEY (arcode)
REFERENCES mrea (arcode) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (OIDS=FALSE);
ALTER TABLE mscn OWNER TO postgres;
/*mstandard table*/
CREATE TABLE mstandard
(
standardcode character varying(12) NOT NULL,
oldstandardtdcode character varying(12) NOT NULL,
"level" character varying(3) NOT NULL,
title text NOT NULL,
ststartdate date,
stenddate date,
registdate date NOT NULL,
updatedate date NOT NULL,
stdintrsv1 integer,
stdintrsv2 integer,
stddatersv1 date,
stddatersv2 date,
stdtxtrsv1 text,
stdtxtrsv2 text,
CONSTRAINT mstandard_pkey PRIMARY KEY (standardcode)
)
WITH (OIDS=FALSE);
ALTER TABLE mstandard OWNER TO postgres;
SQL i have used:
select ((select count(*) from tdos,tdlr,mscn,mstandard
where tdos.standardcode = mstandard.standardcode and
tdos.dlrcode=tdlr.dlrcode and
tdlr.scncode = mscn.scncode and
tdos.dosresult = 1 and
mscn.scncode = '' and
tdos.dosnakbn = 0 and
tdos.dosdatersv1 between '2006/11/1' and '2007/4/1')) as Periodone,
(select count(*) from tdos,tdlr,mscn,mstandard
where tdos.standardcode = mstandard.standardcode and
tdos.dlrcode=tdlr.dlrcode and
tdlr.scncode = mscn.scncode and
mscn.scncode = '' and
tdos.dosresult = 1 and
tdos.dosnakbn = 0 and
tdos.dosdatersv1 between '2007/4/1' and '2008/11/1' ) as Periodtwo,
Round(((select count(*) from tdos,tdlr,mscn,mstandard
where tdos.standardcode = mstandard.standardcode and
tdos.dlrcode=tdlr.dlrcode and
tdlr.scncode = mscn.scncode and
mscn.scncode = '' and
tdos.dosresult = 1 and
tdos.dosnakbn = 0 and
tdos.dosdatersv1 between '2007/4/1' and '2008/11/1') -
(select count(*) from tdos,tdlr,mscn,mstandard
where tdos.standardcode = mstandard.standardcode and
tdos.dlrcode=tdlr.dlrcode and
tdlr.scncode = mscn.scncode and
tdos.dosresult = 1 and
tdos.dosnakbn = 0 and
mscn.scncode = '' and
tdos.dosdatersv1 between '2006/11/1' and '2007/4/1'))/(1.0*((select count(*)
from tdos,tdlr,mscn,mstandard
where tdos.standardcode = mstandard.standardcode and
tdos.dlrcode=tdlr.dlrcode and
tdlr.scncode = mscn.scncode and
tdos.dosresult = 1 and
tdos.dosnakbn = 0 and
mscn.scncode = '' and
tdos.dosdatersv1 between '2007/4/1' and '2008/11/1')))*100,0)
As Growthrate.
in this SQL i calculated the result only for one scncode.
i need this result for all scncode under mscn table.
If you have other logic to calculate this,please tell me the logic
Thanks in advance.
John.
Re: [SQL] JOIN results of refcursor functions
>
> Then I've tried:
>
> CREATE FUNCTION foo(insklid int, out sklid int, out elid INT) RETURNS SETOF
> record AS $$
> BEGIN
>RETURN QUERY SELECT sklid,elid FROM skladkol;
>RETURN;
> END;
> $$ LANGUAGE plpgsql;
know bug :( - your variable names are in collision with column names.
You have to protect self - use prefixes for variables
postgres=# create table a(a varchar, b int);
CREATE TABLE
postgres=# insert into a values('kuku',10),('juku',20);
INSERT 0 2
postgres=# create function f(out _a varchar, out _b integer) returns
setof record as $$begin return query select * from a; return; end; $$
language plpgsql;
CREATE FUNCTION
postgres=# select * from f();
_a | _b
--+
kuku | 10
juku | 20
(2 rows)
regards
Pavel Stehule
>
> but it returns 5498 rows (which is exact number of rows in that table) but
> of NULL values. WHAT AM I DOING WRONG ? :(((
>
> I'm using EMS Manager 2007 Lite for PostgreSQL. That shouldn't be a problem,
> right ?
>
> Regards,
>
> Milan Oparnica
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
