[SQL] sequence

[cristi]

What is wrong here?

insert into table_name (field_name) values (select
setval('sequence_name')-1) as currval);



---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] sequence

[Dennis Björklund]

On Fri, 15 Aug 2003, cristi wrote:

> What is wrong here?
> 
> insert into table_name (field_name) values (select
> setval('sequence_name')-1) as currval);

Your probably want this instead:

  insert into table_name (field_name) values (nextval('sequence_name'));

The reason why your insert fail above is that setval() should have more 
parameters, but even if it had worked it does not make sense to call 
setval() there. See

  http://www.postgresql.org/docs/7.3/static/functions-sequence.html

Also, it's easier to use a serial column:

  http://www.postgresql.org/docs/7.3/static/datatype.html#DATATYPE-SERIAL

then you can do

  insert into table_name (field_name) values (DEFAULT);

-- 
/Dennis


---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [SQL] sequence

[Bertrand Petit]

On Fri, Aug 15, 2003 at 12:32:36PM +0300, cristi wrote:
> What is wrong here?
> 
> insert into table_name (field_name) values (select
> setval('sequence_name')-1) as currval);

This should be better:

INSERT INTO table_name (field_name) VALUES (nextval('sequence_name'));

-- 
%!PS
297.6 420.9 translate 90 rotate 0 setgray gsave 0 1 1{pop 0 180 moveto 100
180 170 100 170 -10 curveto 180 -9 180 -9 190 -10 curveto 190 100 100 180
0 180 curveto fill 180 rotate}for grestore/Bookman-LightItalic findfont
240 scalefont setfont -151.536392 -63.7998886 moveto (bp)show showpage

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [SQL] sequence

[Tom Lane]

"cristi" <[EMAIL PROTECTED]> writes:
> What is wrong here?
> insert into table_name (field_name) values (select
> setval('sequence_name')-1) as currval);

Either too few parentheses, or too many ;-)

You could write this as an INSERT/SELECT:

insert into table_name (field_name)
  select setval('sequence_name')-1 as currval;

or you could write it as an INSERT/VALUES with scalar subquery
expression:

insert into table_name (field_name)
  values ((select setval('sequence_name')-1 as currval));

(all the parentheses are required here).  But really you do not need
a subquery for this at all; VALUES is perfectly content with scalar
expressions:

insert into table_name (field_name)
  values (setval('sequence_name')-1);

regards, tom lane

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] About primary keys.

[Tim Andersen]

I'm new to PostgreSQL but I am familiar with DB2,
Oracle and Sybase.  I must say, I am impressed with
PostgreSQL so far!

In order to compare databases across DBMS platforms,
we need to create a view that queries from the system
catalog tables.  This view returns all of the columns
in the database listed by schema, table, and
columnname with some additional information about the
column (such as a primary key indicator).

These are the columns in the view:
creator (schema), tname (tablename), cname
(columnname), coltype (datatype), nulls (nullable),
length, syslength (precision), in_primary_key, colno
(columnumber), default_value, comments

I looked in the archives at postgresql.com, and I
found someone else with the same problem that I had
but no solution was posted.

I have made some good progress on creating a view that
selects from system catalog tables, but I am having
trouble with the in_primary_key and the
length/precision columns.  Many of our tables have
complex primary keys.

The query I have so far only gets columns that are
part of a primary key.  I need to return all of the
columns listed and a Y/N indicator for whether or not
the column is a part of the tables primary key.
Here's what I have:
/*---//
// This view shows all rows that //
// are part of a primary key://
//---*/
select upper(pgt1.schemaname) as "creator",
   upper(pgt1.tablename) as "tname",
   upper(pga1.attname) as "cname",
   case smmtsys.v_datatype.typname
 when 'bpchar' then 'char'
 else smmtsys.v_datatype.typname
   end as "coltype",
   case pga1.attnotnull
 when true then 'N'
 when false then 'Y'
   end as "nulls",
   i.indisprimary as "in_primary_key",
   pga1.atttypmod as "length",
   pga1.attndims as "syslength",
   pga1.attnum as "colno"
  from pg_tables pgt1,
   pg_class pgc1,
   pg_attribute pga1,
   pg_attribute pga2,
   pg_type,
   smmtsys.v_datatype,
   pg_index i,
   pg_namespace n
 where pgc1.relname = pgt1.tablename
   and pg_type.typname = pgt1.tablename
   and pga1.attrelid = pgc1.relfilenode
   and  pga1.attnum > 0
   and pga1.atttypid = smmtsys.v_datatype.oid
   and pgc1.oid = i.indrelid
   and i.indisprimary = 't'
   and n.oid = pgc1.relnamespace
   and pgt1.tablename = pgc1.relname
   and pga2.attrelid = i.indexrelid
   and pga1.attrelid = i.indrelid
   and pga1.attnum = i.indkey[pga2.attnum-1];

/*---//
// this is a quick and dirty //
// view to get the datatypes //
// used in the above query:  //
//---*/ 
create view smmtsys.v_datatype as (
select oid, typname from pg_type)
;


__
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [SQL] About primary keys.

[Tom Lane]

Tim Andersen <[EMAIL PROTECTED]> writes:
> The query I have so far only gets columns that are
> part of a primary key.
>...
>and pga1.attnum = i.indkey[pga2.attnum-1];

This is wrong because you are looking at only one indkey position, and
the attribute could be in any position of the primary key.  I think
what you want is to drop pga2 from the query and instead use something
like

... and pga1.attnum in (i.indkey[0], i.indkey[1], i.indkey[2], ...)

(carrying it out to whatever you think is a reasonable upper bound on
the number of columns in a primary key --- the normal Postgres limit
is 32 keys but I can't believe anyone would use that many in practice).

regards, tom lane

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [SQL] About primary keys.

[Tim Andersen]

I looked in the info.c on line 2891 of the
psqlodbc-7.2.5 to find this SQL logic (courtesy of Tom
Lane)
select ta.attname, ia.attnum
 from pg_attribute ta, pg_attribute ia, pg_class c,
pg_index i, pg_namespace n
 where c.oid = i.indrelid
 AND n.oid = c.relnamespace
 AND i.indisprimary = 't'
 AND ia.attrelid = i.indexrelid
 AND ta.attrelid = i.indrelid
 AND ta.attnum = i.indkey[ia.attnum-1];

The above SQL retrieves each and every column in the
database that is a part of a complex primary key.
I need to join this to a list of all of the columns in
the database so I can have the primary key indicator. 


Here's another variation of the above SQL that shows
schema, table, column, colum_num, and a primary key
indicator:

select pg_tables.schemaname, pg_tables.tablename,
ta.attname, ia.attnum, i.indisprimary
from pg_attribute ta, pg_attribute ia, pg_class c,
pg_index i, pg_namespace n , pg_tables
where c.oid = i.indrelid
AND n.oid = c.relnamespace
AND i.indisprimary = 't'
AND ia.attrelid = i.indexrelid
AND ta.attrelid = i.indrelid
AND ta.attnum = i.indkey[ia.attnum-1]
AND pg_tables.tablename = c.relname;

so, shouldn't there be an easy way to retrieve all of
the columns for all tables with a primary key
indicator using this strategy?

If creating another view will simplify syntax, that's
fine too.


__
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [SQL] About primary keys -- made some progress

[Tim Andersen]

This might not be the cleanest solution, but it runs
fast and it retrieved the information I need.
I broke it down into pieces and created several views
to query from to simplify it for myself.
The first four statements are views and the last one
is the query I was originally trying to get.  (note
that smmtsys is a schema I created, everything else is
dealing with system catalog tables)
Here's the SQL:
---
create view smmtsys.v_datatype as (
select oid, typname from pg_type)
;

create view smmtsys.v_primarykeys as(
select pg_tables.schemaname, pg_tables.tablename,
ta.attname, ta.attrelid, ia.attnum, i.indisprimary
from pg_attribute ta, pg_attribute ia, pg_class c,
pg_index i, pg_namespace n , pg_tables
where c.oid = i.indrelid
AND n.oid = c.relnamespace
AND i.indisprimary = 't'
AND ia.attrelid = i.indexrelid
AND ta.attrelid = i.indrelid
AND ta.attnum = i.indkey[ia.attnum-1]
AND pg_tables.tablename = c.relname
AND (pg_tables.schemaname = 'summit' or 
pg_tables.schemaname = 'uhelp' or 
pg_tables.schemaname = 'smmtsys' or 
pg_tables.schemaname = 'smmtsec' or 
pg_tables.schemaname = 'smmtccon' )
and ta.attname > 0
)
;

create view smmtsys.v_allcolumns as (
select pg_tables.schemaname,
   pg_tables.tablename,
   pg_attribute.attname
from pg_tables, 
 pg_class, 
 pg_attribute, 
 smmtsys.v_datatype 
where (schemaname = 'smmtccon' or 
   schemaname = 'smmtsec' or 
   schemaname = 'smmtsys' or 
   schemaname = 'summit' or 
   schemaname = 'uhelp' ) and 
  pg_class.relname = pg_tables.tablename and 
  pg_type.typname = pg_tables.tablename and 
  pg_attribute.attrelid = pg_class.relfilenode and

  pg_attribute.attnum > 0 and 
  pg_attribute.atttypid = smmtsys.v_datatype.oid
)
;

create view smmtsys.v_primarykeyind as (
select cols.schemaname ,
   cols.tablename ,
   cols.attname,
   case pks.indisprimary
 when true then 'Y'
 else 'N'
   end as in_primary_key
from smmtsys.v_allcolumns cols left outer join
smmtsys.v_primarykeys pks
on (cols.schemaname = pks.schemaname
and cols.tablename = pks.tablename
and cols.attname= pks.attname)
);

select upper(tbls.schemaname) as "creator", 
   upper(tbls.tablename) as "tname", 
   upper(cols.attname) as "cname", 
   case smmtsys.v_datatype.typname
 when 'bpchar' then 'char'
 else smmtsys.v_datatype.typname
   end as "coltype", 
   case cols.attnotnull
 when true then 'N'
 when false then 'Y'
   end as "nulls",
   length(cols.attrelid) as "length",  
   cols.attndims as "syslength", 
   vpk.in_primary_key,
   cols.attnum as "colno"
from pg_tables tbls, 
 pg_class, 
 pg_attribute cols, 
 pg_type, 
 smmtsys.v_datatype,
 smmtsys.v_primarykeyind vpk
where (tbls.schemaname = 'smmtccon'
   or tbls.schemaname = 'smmtsec'
   or tbls.schemaname = 'smmtsys'
   or tbls.schemaname = 'summit'
   or tbls.schemaname = 'uhelp')
  and pg_class.relname = tbls.tablename
  and pg_type.typname = tbls.tablename
  and cols.attrelid = pg_class.relfilenode
  and cols.attnum > 0
  and cols.atttypid = smmtsys.v_datatype.oid
  and vpk.schemaname = tbls.schemaname
  and vpk.tablename = tbls.tablename
  and vpk.attname = cols.attname
;

This retrieves all of the columns and shows a primary
key indicator for each column.  If someone could put
this logic all into one SQL query, I'd really like to
see it!

I still have a question about how to get the
information about length and precision of a column
from pg_attributes.atttypmod.  are there built-in
functions for PostgreSQL to extract this information?
Additionally, I need to get the column default value
and the comments on the column, but I think I can
figure that out with a little more time.



__
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com

---(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] How to optimize this query ?

[ProgHome]

As I am using mysql
4.0 right now (we’ve got a stupid problem with the 4.1 with the authentification protocol we can’t figure out) and the last
subquery (the one in the last LEFT JOIN) MUST be
removed …

 

So I tried the following query:

 

SELECT
   L.*

FROM lead L
LEFT JOIN purchase P1 ON ( L.id = P1.lead_id ) 
LEFT JOIN affiliate_lockout A ON ( L.affiliate_id = A.affiliate_locked_id ) 
LEFT JOIN ( 

purchase P2
INNER JOIN member_exclusion M ON ( P2.member_id =
M.member_id_to_exclude) 

) ON ( L.id = P2.lead_id ) 
WHERE UNIX_TIMESTAMP( now( ) ) - UNIX_TIMESTAMP( date_creation ) <= ( 6 * 24 * 3600 ) AND ( 

exclusive IS NULL OR ( 

exclusive = 0 AND nb_purchases < 3

)

) AND ( 

A.member_id <> 21101
OR A.member_id IS NULL ) 

AND ( P1.member_id <>
21101 OR P1.member_id IS NULL )

 

But it seems that the LEFT JOIN doesn’t work anymore and are replaced
by OUTER JOIN because the result of the query is (number of rows in Lead *
number of rows in PURCHASE * number of rows in …)

And it seems that the condition L.id = P2.lead_id doesn’t work either …

 

Could you tell me what
the problem is ?

Thanks

 

 

-Original Message-
From: Franco Bruno Borghesi
[mailto:[EMAIL PROTECTED] 
Sent: Wednesday, August 13, 2003 12:18 PM
To: [EMAIL PROTECTED];
[EMAIL PROTECTED]
Subject: Re: [SQL] How to optimize
this query ?

 

Maybe its better now. Anyway, what I think is that joining will perform better than using IN. Am I wrong?

SELECT
   L.*
FROM
  lead L
  LEFT JOIN purchase P ON (L.id=P.lead_id)
  LEFT JOIN member_exclusion M ON (P.member_id=M.member_id_to_exclude)
  LEFT JOIN (
       SELECT DISTINCT affiliate_locked_id FROM affiliate_lockout WHERE member_id=21101
    ) A ON (L.affiliate_id=A.affiliated_locled_id)
WHERE
    L.exclusive IS NULL OR
    (
    L.exclusive=0 AND
    L.nb_purchases<3
    ) AND
    (P.lead_id IS NULL OR P.lead_id<>21101) AND
  (M.member_id IS NULL) AND
    (A.member_id IS NULL)
  

Re: [SQL] Optional join

[David Fetter]

"Slawek Jarosz" <[EMAIL PROTECTED]> wrote:
> Hi,

> I trying to write a query that will join 2 tables.   Here's the
> concept:

> Table 1: table1, primary key pk1
> Table 2: table2, primary key pk2

> One of the fields (f2) in table2 contains either the primary key of
> table1 or a NULL value.  So normally a pretty basic query:

> SELECT table1.*, table2.pk2
> FROM table1, table2 WHERE table2.f2 = table1.pk1;  

> BUT what I would like to do is show all records of Table 1 even if
> there is no match in Table 2.  Meaning that the reults could be 

> table1...   table2.pk2
> table1...   NULL

> Doable?

Yes.

SELECT table1.*, table2.pk2
FROM table1 LEFT OUTER JOIN table2 ON (table1.pk1 = table2.pk2);

HTH :)

Cheers,
D
-- 
David Fetter [EMAIL PROTECTED] http://fetter.org/
phone: +1 510 893 6100cell: +1 415 235 3778

Power over a man's subsistence is power over his will.
Alexander Hamilton

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[SQL] [Newbie] migrating a stored procedure from MSSQL to postgresql

[Bengali]

Hi,
I am a postgresql and stored procedures beginner and I
would like to know if the stored procedure I am trying to migrate
to plpgsql from MSSQL is correct.
Here 's the only table involved in the stored procedure:
create table ManufacturerOrders
(
OrderNumber serial,
SKU int not null,
Makevarchar(50) not null,
Model   varchar(50) not null,
Price   int not null,
Status varchar(20) not null,
primary key (OrderNumber)
);
Here 's the original MSSQL stored procedure:
create procedure UpdateOrder (@OrderNum int)
as
set nocount on
update ManufacturerOrders set Status = "Shipped" where
OrderNumber = @OrderNum;
SELECT SKU, Price FROM ManufacturerOrders
WHERE OrderNumber = @OrderNum
go
Here 's the plpgsql version i wrote:

CREATE FUNCTION UpdateOrder(INTEGER) RETURNS TEXT AS '
 DECLARE
  i_ordernum ALIAS for $1;
  r_SKUPrice RECORD;
 BEGIN
update ManufacturerOrders set Status = ''Shipped'' where 
OrderNumber = i_ordernum;

SELECT SKU, Price INTO r_SKUPrice FROM ManufacturerOrders WHERE 
OrderNumber = i_ordernum;
return r_SKUPrice;

 END;
 ' LANGUAGE 'plpgsql';
I would like to know especially if the RETURNS statement is correct here
and if i can give a name to the record r_SKUPrice columns .
Thanks in advance,
Bengali
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings