[SQL] Re: Help with query. (*)
[NOTE: I'm a pgsql newbie myself. Take this reply with a large-ish grain of salt!) Shouldn't it be something straightforward like: select a.a, a.b, a.c, ... from a a, b b where a.x = b.x, and a.y = b.y, ... (I'd watch out for too many clauses here... if you've got a lot of clauses, you're probably not normalized as much as you should be.) If you have indexes on the relevant fields, you shouldn't get a table scan and this should return rather quickly, right? -Ken "Diehl, Jeffrey" wrote: > > I'm having difficulty writing a query which I really can't live without... > > I need to get a list of records from table A for which there are > corresponding records in table B. I've tried to use the intersect clause, > but it doesn't seem to work, or it runs far too long. For example: > > select * from A > where 1=1 > intersect select * from A where > B.x=A.x > and A.y=B.y > and A.z=B.z > limit 100 > > I need the most efficient method possible; my A tables have upward of 5 > Million records. The B table, btw, only has about 100 records. > > Any help will be most appreciated.
[SQL] Re: binary operators
Frederic Metoz wrote: > I am looking for the binary AND and OR ... SHIFT as well. > Do they exist for postgresql ? Depending on what you're doing...you might get away with using mathematical operators to accomplish the above... A right SHIFT would be dividing by 2. A left shift would be multiplying by 2. I don't know off the top of my head about AND and OR. Doesn't the manual cover this? -Ken
[SQL] Hrm...why is this wrong?
In trying to use a plpgsql stored proc, I'm getting an error I don't
understand.
When the select at the bottom of this email is executed, I'm getting the
message:
ERROR: parser: parse error at or near "$1"
Any ideas?
--
Ken Corey, CTOAtomic Interactive, Ltd.
select 'drop FUNCTION IU_EMPLOYEE(varchar(255), ...);' as Progress;
drop FUNCTION IU_EMPLOYEE( varchar(255), varchar(255),
varchar(255), varchar(255), varchar(255), varchar(255),
varchar(255), varchar(255), varchar(255), varchar(255),
varchar(255));
select 'create FUNCTION IU_EMPLOYEE(varchar(255), ...)' as Progress;
create FUNCTION IU_EMPLOYEE( varchar(255), varchar(255),
varchar(255), varchar(255), varchar(255), varchar(255),
varchar(255), varchar(255), varchar(255), varchar(255),
varchar(255))
RETURNS INT4
AS '
DECLARE
user_name_in alias for $1;
passwd_in alias for $2;
firstname_in alias for $3;
lastname_in alias for $4;
company_in alias for $5;
addr1_in alias for $6;
addr2_in alias for $7;
city_in alias for $8;
state_in alias for $9;
postcode_in alias for $10;
country_in alias for $11;
userid_calc INT4;
companyid_calc INT4;
BEGIN
userid_calc := 0;
select into companyid_calc COMPANY_ID from COMPANY
where COMPANY_NAME = company_in;
if (companyid_calc is null)
then
insert into COMPANY (COMPANY_NAME) values (company_in);
companyid_calc := currval(''company_company_id_seq'');
end if;
if (companyid_calc is not null)
then
insert into EMPLOYEE ( COMPANY_ID ,
AUTHORIZED , RIGHTS , USERNAME , PASSWD , FIRSTNAME ,
LASTNAME , ADDR1 , ADDR2 , CITY , STATE , POSTCODE ,
COUNTRY)
values (
companyid_calc,0,0,username_in, password_in, firstname_in,
lastname_in, company_in,addr1_in,addr2_in,city_in,
state_in,postcode_in,country_in
);
userid_calc := currval(''employee_employee_id_seq'');
else
rollback;
return 0;
end if;
return userid_calc;
END;'
LANGUAGE 'plpgsql';
select iu_employee('handtest','password','hand','test','handcompany',
'handaddr','handaddr2','handcity','handstate','handpostcode','handcountry');
Re: [SQL] Hrm...why is this wrong?
Wow! Answering emails on a Sunday? Someone should be giving you an award or something. On Sunday 04 February 2001 8:13 pm, you wrote: > Ken Corey <[EMAIL PROTECTED]> writes: > > When the select at the bottom of this email is executed, I'm getting the > > message: > > ERROR: parser: parse error at or near "$1" > > I don't get that; I get > ERROR: Attribute 'username_in' not found > which is about what I'd expect for the given function text; maybe you > didn't transcribe it accurately? That's strange...perhaps the difference was a problem with my table definition? *shrug* I also had made a few mistakes, so once I got those fixed, the code seems to work again. > Anyway, an invaluable technique for debugging plpgsql functions is to > start psql with debug level 2, so that the queries the plpgsql executor > feeds to the SQL engine get logged in the postmaster log. (If you don't > run the postmaster with a logfile, you should...) For example: Hey, that's perfect. It's okay just so long as the debugging out goes *somewhere*...:^) Thanks, Tom. -- Ken Corey, CTOAtomic Interactive, Ltd.
[SQL] Transactions in PLPGSQL?
Hi All! Are the BEGIN/END; seen in a typical PL/PGSQL function a transaction wrapper, or do I need to add another BEGIN/END block? Should I just put a 'rollback' in the function, or do I need to do something special? Thanks! -Ken
[SQL] My apologies...
My apologies to the lists...in trying to stop my own spam, I spammed you all. This has now been fixed. Again, sorry for any inconvenience. -Ken
[SQL] Silly question about numbering of rows?
Hi All, I haven't posted a silly question in a while, and didn't want to break my streak... I have a table like this: id | make | model | year | value -+---++---+--- 57 |2 |0 | 4 | 4750 57 |2 |3 | 4 | 4750 57 |2 |0 | 0 | 4750 57 |2 |0 | 3 | 4750 57 |2 |3 | 0 | 4750 57 |2 |3 | 3 | 4750 2 |2 |0 | 3 | 4750 2 |2 |3 | 3 | 4750 2 |2 |0 | 4 | 4350 2 |2 |3 | 4 | 4350 2 |2 |0 | 0 | 4750 2 |2 |0 | 5 | 4750 2 |2 |3 | 0 | 4750 2 |2 |3 | 5 | 4750 I want to be able to select from this table like this: select , id, value from mytable where make=2 model=3 and year=5 order by score desc; which would return this: nbring |id | value ---+-+--- 1 | 57 | 4750 2 | 2 | 4350 Everything is working swimmingly, except for the numbering magic. As you can see there are several variations of data in that table, so I can't use a serial on the table directly. Further, this table is likely to change on a minute by minute basis, so I don't want to create a permanent numbering that will just have to be changed. I thought about creating a temporary table with a serial and selecting into that so that a numbering is created and then returning rows from that, but that sounds like much busywork for the database. It sounds easiest to me to just punt and number the rows as they are returned in my calling application... What's the best approach here? -- Ken Corey, CTOAtomic Interactive, Ltd. [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[SQL] Fwd: Silly question about numbering of rows?
Hi All, I haven't posted a silly question in a while, and didn't want to break my streak... I have a table like this: id | make | model | year | value -+---++---+--- 57 |2 |0 | 4 | 4750 57 |2 |3 | 4 | 4750 57 |2 |0 | 0 | 4750 57 |2 |0 | 3 | 4750 57 |2 |3 | 0 | 4750 57 |2 |3 | 3 | 4750 2 |2 |0 | 3 | 4750 2 |2 |3 | 3 | 4750 2 |2 |0 | 4 | 4350 2 |2 |3 | 4 | 4350 2 |2 |0 | 0 | 4750 2 |2 |0 | 5 | 4750 2 |2 |3 | 0 | 4750 2 |2 |3 | 5 | 4750 I want to be able to select from this table like this: select , id, value from mytable where make=2 model=3 and year=5 order by score desc; which would return this: nbring |id | value ---+-+--- 1 | 57 | 4750 2 | 2 | 4350 Everything is working swimmingly, except for the numbering magic. As you can see there are several variations of data in that table, so I can't use a serial on the table directly. Further, this table is likely to change on a minute by minute basis, so I don't want to create a permanent numbering that will just have to be changed. I thought about creating a temporary table with a serial and selecting into that so that a numbering is created and then returning rows from that, but that sounds like much busywork for the database. It sounds easiest to me to just punt and number the rows as they are returned in my calling application... What's the best approach here? -- Ken Corey, CTOAtomic Interactive, Ltd. [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Re: Re: Using Random Sequence as Key
First of all, let me apologise for stepping so far back into the archives,
but I wanted to get this in the archives in case anyone else wanted to use
this.
There's a slight addendum here...as written, it's fairly likely that people
will get duplicate ID's using this system.
A small change gets rid of the problem: multiply the serialportion by the
number of digits you'll have in timeportion. So, if timeportion is three
digits, you'd multiply serialportion by 1000 and *then* add the timeportion.
Below, I use the mod operator '%' to make sure it's 3 digits or less.
Caveat: this will only work as long as you have less than 40 million users.
;^)
-Ken
create FUNCTION new_request_id()
RETURNS INT4
as
'
DECLARE
timeportion INT4;
serialportion INT4;
BEGIN
timeportion := cast
(date_part(''milliseconds'',timeofday()::timestamp) as integer);
serialportion := nextval(''request_idfake_seq'')* 1000;
RETURN (timeportion % 1000) + serialportion;
END;'
LANGUAGE 'plpgsql'
;
On Tuesday 17 April 2001 3:08 pm, Josh Berkus wrote:
> Bernardo,
>
> > I needed the random field because if I use serial and the user gets a
> > 34203
> > he's sure that 34202 exists, and that (probably, there where 34202
> > inserts
> > before him (or at least an offset + some)). Using a random just makes
> > the
> > user totally blind.
> > As I said I could use a serial for indexing the table but I NEED the
> > random
> > field and I need to to be unique since all the queries will be using
> > it as a
> > search parameter.
> > If inserting this way is slow it's not such a big deal since it's a
> > small db
> > and inserts are seldom made.
> > Thanks in advance for any help.
>
> Here's another suggestion for you then:
>
> 1. Add a sequence "Sales_sq"
>
> 1. write a custom function for new id numbers:
> CREATE FUNCTION new_sales_id() RETURNS INT4 AS '
> DECLARE
> timeportion VARCHAR;
> serialportion INT4;
> BEGIN
> timeportion := to_char(current_timestamp, ''ms'');
> -- (or whatever the abbreviation for 2-digit milliseconds is)
> serialportion := 100*(nextval(''sales_seq''));
> RETURN CAST(to_number(timeportion) AS INT4) + serialportion;
> END;
>
> 3. Then set the id column to default to this new function.
>
> This would give you (after you correct my mistakes) a number, the first
> X digits of are Serial, and the last 2 digits based on the server's
> internal clock. Thus, the numbers would *not* be sequential, and would
> appear fairly random, but would be unique *without* and expensive check
> for that value anywhere in the table for each insert.
>
> -Josh Berkus
>
>
>
>
>
>
> __AGLIO DATABASE SOLUTIONS___
>Josh Berkus
> Complete information technology [EMAIL PROTECTED]
> and data management solutions (415) 565-7293
> for law firms, small businessesfax 621-2533
> and non-profit organizations. San Francisco
>
> ---(end of broadcast)---
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
--
Ken Corey, CTOAtomic Interactive, Ltd. [EMAIL PROTECTED]
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Returning PK of first insert for second insert use.
On Mon, 2002-07-29 at 19:32, Peter Atkins wrote:
> I have two tables t_proj, t_task see below:
>
> CREATE TABLE t_proj (
> proj_id SERIAL NOT NULL,
> PRIMARY KEY (proj_id),
> task_id integer(12),
> user_id integer(6),
> title varchar(35),
> description varchar(80)
> );
>
> CREATE TABLE t_task (
> task_id SERIAL NOT NULL,
> PRIMARY KEY (task_id),
> title varchar(35),
> description varchar(80)
> );
>
> When I insert into t_task I need to return the task_id (PK) for that insert
> to be used for the insert into the t_proj table.
>
> I tried using RESULT_OID but I have no idea how to obtain the true PK using
> this opague id. Below is the procedure I tried to use.
Since the primary key of the first table is a SERIAL, it's really
defined as something like this:
create table t_task (
task_id int4 not null default nextval('t_task_task_id_seq'),
...
Which means that you can predict what the next value will be, store that
in a temporary var, and then insert it into both tables...
CREATE OR REPLACE FUNCTION insertTask (varchar, varchar)
RETURNS INTEGER AS '
DECLARE
-- local variables
oid1 INTEGER;
retval INTEGER;
tempvar int4;
BEGIN
select into tempvar nextval(''t_task_task_id_seq'');
INSERT INTO t_task (task_id, title, description)
VALUES (tempvar,$1, $2);
-- Everything has passed, return id as pk
RETURN tempvar;
END;
' LANGUAGE 'plpgsql';
WARNING: this is not guaranteed to be the correct syntax, I didn't
create the tables and the function to test it, but I do this kind of
thing all the time in my functions.
--
Ken Corey CTO http://www.atomic-interactive.com 07720 440 731
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] Returning PK of first insert for second insert use.
On Mon, 2002-07-29 at 20:52, Peter Atkins wrote: > Is there a possibility of another application accessing the DB and using the > id before my function has completed the transaction? I'm concerned with the > possibility of cross-over of ID's if the insert hangs. > > There's no way to return the id of that insert inherently, and then use it > for the second insert? I think SQL uses something like ADD_ID, not sure. That's the beauty of the nextval statement. The database internally sequences requests to it so that you're kept out of harm's way. Say process A called the function,and nextval returns 16. The function now continues on its way, but is not finished when process B then calls the function (before A is done), and nextval returns 17. So, then function called by process A returns 16, and the function called by process B returns 17. That means that unless the results of process B depend in some way upon the results of process A, there's no problem. -Ken -- Ken Corey CTO http://www.atomic-interactive.com 07720 440 731 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] VACUUM not doing its job?
I've run into this myself. Tom lane helped me out. In my case, it was the fact that indexes don't release the space of indexes of deleted rows. So, if you have a table that has a lot of inserts/deletes, your indexes will grow incredibly fast. The way to see what your biggest items are: select * from pg_class order by relpages desc; If your tables are active with lots of inserts/deletes, the biggest things will likely be indexes. The only way that I know to recover this space is to drop the indexes and recreate them. Vacuum didn't touch them for me. -Ken On Sat, 2002-08-03 at 21:53, Kristian Eide wrote: > Three week ago we did a pg_dump/pg_restore, and after pgsql/data/ took about > 750MB. Now it uses 2,4GB of disk space! If we do a new complete dump and > restore the volume will decrease to about 800MB. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
