[SQL] need to debug
Could someone provide a link or help me understand what is required to get function debugging working. I haven't started yet and I thought it best to check with you guys before I start. Just the little I have researched has me worried and I do not want to mess up my current install. I'm on openSUSE 11.3 using postgres 8.4 Thanks in advance, Johnf -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] a general ? on select performance
Hi, In general what are the causes of SQL select statement slow downs - other than the size of the data? I realize this is a broad question but read on please. Here's where I'm coming from: I have program that loops though several thousands of records. The loop contains a select statement that confirms that I don't have a dup and then inserts a record into a different table. IOW's the statement is checking against a static table. The size of the table I'm checking does not change during the loop. I'm sure many have done something similar. The loop performs very well for the first 500 - 800 items. Then starts slowing down. And somewhere around 1200 really slows to a crawl. To the point it does not complete in a 12 hour period. But when I take several of the statements and execute them using pgAdmin - they take almost no time (0.001 if memory serves) for rec in check_set_data: select pkid from check_table where check_field = rec.field_name if found skip else insert into new table. I'm using 8.4 on openSUSE 11.3 multi-processor, 8 GB of ram. Johnf -- 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] a general ? on select performance
On Saturday, August 28, 2010 08:35:07 am Tom Lane wrote: > John Fabiani writes: > > I have program that loops though several thousands of records. The loop > > contains a select statement that confirms that I don't have a dup and > > then inserts a record into a different table. IOW's the statement is > > checking against a static table. The size of the table I'm checking > > does not change during the loop. I'm sure many have done something > > similar. > > Are you really, truly making *no* changes to the table you're reading? > > What I'm suspecting is that you are making such changes, in fact lots > of them. The number of live rows may not change, but the table is > bloating with lots of dead row versions, which can't be cleaned up yet > since the transaction that deleted them is still open. This leads to > O(N^2) or worse slowdown. > > There are usually ways around this type of problem, but we'd need more > details about what you're really doing. > > regards, tom lane I'm not making any changes to the lookup table. But I did discover the performance problem - rather I was helped into the light (used the IRC). There is a custom function in my select statement. It turns out that the function was labeled as "VOLATILE" and a simple change to 'STABLE' made all the difference in the world. I was not aware of this setting (I won't forget it). Learn something everyday! But your answer has me thinking. I often do exactly what you are describing (of course not for thousands of records). So how does one get around the issues bloat? Johnf
[SQL] insert from a select
Hi,
I have a strange issue that is mostly likely me not understanding something.
I always thought that an insert statement would accept any select statement.
I'm guessing I am wrong.
I have created a temporary table ("tempclass") that is exact match to an
existing table ('esclass').
When I attempt to do the following
insert into tempclass Select cl.pkid, cl.depart, cl.sessionid, cl.instrid,
cl.classseq,(select facility from esloc where esloc.pkid = cl.locationid) as
facility, cl.schedule from esclass cl where cl.pkid in (14507,14508)
I get the following error:
ERROR: column "schedule" is of type date but expression is of type character
varying
LINE 1: ... cl.depart, cl.sessionid, cl.instrid, cl.classseq,(select fa...
^
HINT: You will need to rewrite or cast the expression.
The error makes no sense to me. But most important if I just run the select
statement it works perfectly.
Like I said the table "tempclass" (a temporary) is a dup of table "esclass" so
none of it makes sense. Of course I did say I'm missing something.
So why isn't the select statement working with the insert?
Johnf
--
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] insert from a select
On Thursday, November 25, 2010 04:32:57 am Carla wrote:
> Sorry. I forgot some columns:
>
> insert into tempclass (pkid, depart, sessionid, instrid, *classeq,
> facility, schedule*)
> Select cl.pkid, cl.depart, cl.sessionid, cl.instrid,
> cl.classseq,(select facility from esloc where esloc.pkid = cl.locationid)
> as facility, cl.schedule from esclass cl where cl.pkid in (14507,14508)
>
> 2010/11/25 Carla
>
> > Try to explicit the column names. Something like:
> >
> > insert into tempclass (pkid, depart, sessionid, instrid)
> >
> > Select cl.pkid, cl.depart, cl.sessionid, cl.instrid,
> > cl.classseq,(select facility from esloc where esloc.pkid = cl.locationid)
> > as
> > facility, cl.schedule from esclass cl where cl.pkid in (14507,14508)
> >
> > 2010/11/24 John Fabiani
> >
> > Hi,
> >
> >> I have a strange issue that is mostly likely me not understanding
> >>
> >> something.
> >> I always thought that an insert statement would accept any select
> >> statement.
> >> I'm guessing I am wrong.
> >>
> >> I have created a temporary table ("tempclass") that is exact match to an
> >> existing table ('esclass').
> >>
> >> When I attempt to do the following
> >> insert into tempclass Select cl.pkid, cl.depart, cl.sessionid,
> >> cl.instrid, cl.classseq,(select facility from esloc where esloc.pkid =
> >> cl.locationid) as
> >> facility, cl.schedule from esclass cl where cl.pkid in (14507,14508)
> >>
> >> I get the following error:
> >>
> >> ERROR: column "schedule" is of type date but expression is of type
> >> character
> >> varying
> >> LINE 1: ... cl.depart, cl.sessionid, cl.instrid, cl.classseq,(select
> >> fa...
> >>
> >> ^
> >>
> >> HINT: You will need to rewrite or cast the expression.
> >>
> >> The error makes no sense to me. But most important if I just run the
> >> select
> >> statement it works perfectly.
> >>
> >> Like I said the table "tempclass" (a temporary) is a dup of table
> >> "esclass" so
> >> none of it makes sense. Of course I did say I'm missing something.
> >>
> >> So why isn't the select statement working with the insert?
> >>
> >> Johnf
Thanks to all - after reading everyone responses I slept on it. Today I
realized that the order of the fields mattered as you all suggested.
Thanks to all,
Johnf
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] OT - load a shp file
Hi, How do I load a Census shp file into an exist database? I believe I have postGIS install and now I want to load the US counties shp file. the following does not appear to work shp2pgsql -s 4269 -I -W latin1 tl_2008_us_county.shp 2008_us_county | psql Plus I don't know what it does! Johnf -- 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] OT - load a shp file
On Wednesday, December 01, 2010 01:13:52 am Lee Hachadoorian wrote: > John, > > Would probably be useful to see the results of the command, but a couple > of things are immediately obvious. > > First, Postgres requires identifiers to start with a letter or > underscore. You are attempting to create a table named 2008_us_county, > which is not a legal identifier. Also, unless you want this in the > public schema, make sure to use the schema-qualified table name. > > Second, the docs specify that the shapefile be passed in without the > .shp extension. > > Third, since you're not specifying the database explicitly, make sure > that the default makes sense (psql assumes a default of your user name > or reads it from the PGDATABASE environment variable). The examples in > the shp2pgsql docs all explicitly specify a target database with, e.g. > … | psql -d my_db > > If you fix all of this and it still isn't working, I would direct the > output to a file, look at it to make sure it makes sense, then try to > pass the file to psql. > > If you would prefer, there are also a couple of GUIs that can control > shp2pgsql. I've had success with the SPIT (Shapefile to PostGIS Import > Tool) plugin for Quantum GIS (http://qgis.org/). For Windows only you > can use the Shp2PgSQL Graphical Loader (a plugin for pgAdmin III) > available at http://postgis.refractions.net/download/windows/. > > Finally, a better list for PostGIS support is > postgis-us...@postgis.refractions.net. > > --Lee > > On 12/01/2010 01:06 AM, John Fabiani wrote: > > Hi, > > How do I load a Census shp file into an exist database? I believe I have > > postGIS install and now I want to load the US counties shp file. > > > > the following does not appear to work > > shp2pgsql -s 4269 -I -W latin1 tl_2008_us_county.shp 2008_us_county | > > psql > > > > Plus I don't know what it does! > > > > Johnf thanks for the help. Your response already has taught me a few things and I haven't even tried to fix the issues. Johnf -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] something simple but I can't
Hi guys, I trying to return a 0.00 from a function it there are no records found else return the amount. create or replace function danmeans_getpayments(text) returns numeric as $BODY$ declare invoice_num ALIAS FOR $1; _paidamt numeric; BEGIN select sum(aropen_paid) into _paidamt FROM public.aropen where aropen_applyto is not null and (aropen_applyto = $1) ; IF (FOUND) THEN RETURN _paidamt ; END IF; RETURN 0.00 ; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE COST 100 But all I get is either a blank or the amount paid. What am I doing wrong??? Johnf -- 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] something simple but I can't
On Friday, January 28, 2011 07:46:37 pm Jasen Betts wrote: > On 2011-01-29, John Fabiani wrote: > > Hi guys, > > I trying to return a 0.00 from a function it there are no records found > > else return the amount. > > > > select sum(aropen_paid) into _paidamt FROM public.aropen where > > > > aropen_applyto is not null and (aropen_applyto = $1) ; > > > > IF (FOUND) THEN > > > > RETURN _paidamt ; > > > > END IF; > > > > RETURN 0.00 ; > > > > But all I get is either a blank or the amount paid. What am I doing > > wrong??? Johnf > > how many rows does the query return when no rows match the where? > It returns 1 that looks like ( NULL ). > it return 1 row, which is more than zero thus FOUND is TRUE. > > you can fix your function by changing the IF to > > IF _paidamt IS NOT NULL > > > but if you change the sum to > > coalesce(sum(aropen_paid),0.00) > > you can do the task more simply like this: > > create or replace function danmeans_getpayments(text) > returns numeric as > $BODY$ >select coalesce(sum(aropen_paid),0.00) FROM public.aropen where > aropen_applyto is not null and (aropen_applyto = $1) ; > $BODY$ >LANGUAGE 'sql' ; Thanks to all who replied. All of your suggestions would work and it should have been obvious but it wasn't. Johnf -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] quotes etc
Hi,
I would have thought that there would be a simple built-in function that would
escape the quotes as ('D' Andes') to ('D\' Andes'). But I did not see
anything?
I am I wrong?
Johnf
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] is there a refactor
Hi, I would like to have a simple way to retrieve information for a field name. By that I mean have some SQL select that will return all the tables a field name exist within a database. I did not find anything with google but of course google depends on the search string. Thanks in advance, Johnf -- 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] is there a refactor
On Tuesday, April 05, 2011 07:44:51 am Adrian Klaver wrote: > On Tuesday, April 05, 2011 7:27:24 am John Fabiani wrote: > > Hi, > > I would like to have a simple way to retrieve information for a field > > name. By that I mean have some SQL select that will return all the > > tables a field name exist within a database. I did not find anything > > with google but of course google depends on the search string. > > > > Thanks in advance, > > Johnf > > test(5432)aklaver=>SELECT table_name from information_schema.columns where > column_name = 'id'; >table_name Wow that was quick - thanks to all! Johnf -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Dates and NULL's`
Hi, Maybe this is a dumb question but if I have a date field that contains a NULL will it show up when I ask for a where date range for the same date field. Where mydate >= "2011/04/01"::date and mydate<= "2011/04/30"::date With the above where will the NULL's be selected I ask because I was always told that a NULL matches everything and nothing! Johnf -- 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] Dates and NULL's`
On Tuesday, May 10, 2011 10:16:21 am Richard Broersma wrote: > On Tue, May 10, 2011 at 9:48 AM, John Fabiani wrote: > > Where mydate >= "2011/04/01"::date and mydate<= "2011/04/30"::date > > With the above where will the NULL's be selected > > Here is what I get when I try: > > spi=> SELECT NULL::DATE >= '2011-04-01'::DATE AND NULL::DATE <= > '2011-04-30'::DATE; > ?column? > -- > > (1 row) > > > spi=> SELECT (NULL::DATE >= '2011-04-01'::DATE AND NULL::DATE <= > '2011-04-30'::DATE) IS TRUE; > ?column? > -- > f > (1 row) > > > spi=> SELECT (NULL::DATE >= '2011-04-01'::DATE AND NULL::DATE <= > '2011-04-30'::DATE) IS FALSE; > ?column? > -- > f > (1 row) > > > spi=> SELECT (NULL::DATE >= '2011-04-01'::DATE AND NULL::DATE <= > '2011-04-30'::DATE) IS UNKNOWN; > ?column? > -- > t > (1 row) Sorry Richard, I do not understand. It looks like you are saying the NULLS will be returned too Johnf -- 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] Dates and NULL's`
On Tuesday, May 10, 2011 10:30:58 am Richard Broersma wrote: > On Tue, May 10, 2011 at 10:24 AM, John Fabiani wrote: > >> spi=> SELECT NULL::DATE >= '2011-04-01'::DATE AND NULL::DATE <= > >> '2011-04-30'::DATE; > >> ?column? > >> -- > >> > >> (1 row) > > > > It looks like you are saying the NULLS will be returned too > > The WHERE clause will only return rows is the arguments all evaluate > to TRUE. No rows will be returned for rows that cause the WHERE > clause to evaluate to a NULL value. That is what I have seen (so far). But I knew I should ask the question anyway of suffer later. Johnf -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Union out performs the single statement
Hi, I have a SELECT statement that is using the regexp_split_to_table function as follows: ... and fk_topic in (select regexp_split_to_table(eligible_topics, ',')::int from escourse) Normally there are 1 to 3 values in eligible_topics as 46,50,43. The problem is the performance is terrible and I need a way to improve the performance. I have discovered that if I separate the values in the eligible_topics field and create a "union all" the performance is great! The difference is with regexp_split_to_table function = 4 seconds and using the union = 151 ms So the Union looks like; ... fk_topic = 46 ... union all ... fk_topic = 50 ... union all ... fk_topic = 43 ... Of course the problem is creating the unions when I don't know in advance what the number values are in the eligible_topics field. The complete SQL is: select round(miles_between_lat_long(l.latitude::numeric, l.longitude::numeric, c.latitude::numeric, c.longitude::numeric),0) as miles,s.began, s.ended, s.pkid as sessionid,s.stop_close, l.facility, (select count(*) from esenroll r where r.sessionid=s.pkid) as enrolled, l.totalseats, (select count(*) from esclass cl where cl.sessionid=s.pkid and schedule>=current_date) as classesremaining, tp.ccode from essess s join esloc l on l.pkid = s.locationid join esclient c on c.pkid = 36757 join agmisc tp on tp.pkid = s.topic where s.topic in (select regexp_split_to_table(eligible_topics, ',')::int from escourse) group by 1,2,3,4,5,6,7,8,9,10 having (select count(*) from esclass cl where cl.sessionid=s.pkid and schedule>= current_date and schedule <= current_date + 30) > 0 order by 10,1 Without me posting the schema of the database I can see how it would be difficult to determine the best way to tackle this issue. But I'm hoping others will see some major issue I have created within the select. Thanks in advance for any help, Johnf -- 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] Union out performs the single statement
On Thursday, June 23, 2011 08:44:49 am John Fabiani wrote: > Hi, > I have a SELECT statement that is using the regexp_split_to_table function > as follows: > > ... and fk_topic in (select regexp_split_to_table(eligible_topics, > ',')::int from escourse) > > Normally there are 1 to 3 values in eligible_topics as > 46,50,43. > > The problem is the performance is terrible and I need a way to improve the > performance. I have discovered that if I separate the values in the > eligible_topics field and create a "union all" the performance is great! > The difference is with regexp_split_to_table function = 4 seconds and > using the union = 151 ms > > So the Union looks like; > > ... fk_topic = 46 ... > > union all > > ... fk_topic = 50 ... > > union all > > ... fk_topic = 43 ... > > Of course the problem is creating the unions when I don't know in advance > what the number values are in the eligible_topics field. > > The complete SQL is: > select round(miles_between_lat_long(l.latitude::numeric, > l.longitude::numeric, c.latitude::numeric, c.longitude::numeric),0) as > miles,s.began, s.ended, s.pkid as sessionid,s.stop_close, l.facility, > (select count(*) from esenroll r where r.sessionid=s.pkid) as enrolled, > l.totalseats, (select count(*) from esclass cl where cl.sessionid=s.pkid > and schedule>=current_date) as classesremaining, tp.ccode from essess s > join esloc l on l.pkid = s.locationid join esclient c on c.pkid = 36757 > join agmisc tp on tp.pkid = s.topic where s.topic in (select > regexp_split_to_table(eligible_topics, ',')::int from escourse) group by > 1,2,3,4,5,6,7,8,9,10 having (select count(*) from esclass cl where > cl.sessionid=s.pkid and schedule>= current_date and schedule <= > current_date + 30) > 0 order by 10,1 > > Without me posting the schema of the database I can see how it would be > difficult to determine the best way to tackle this issue. But I'm hoping > others will see some major issue I have created within the select. > > Thanks in advance for any help, > Johnf Thanks to all that took the time to review my SQL. I in fact solved the issue by adding a join and moving the date range to the where. Thanks for the help. Johnf -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] interesting sequence
Hi, I have a special need to create a sequence like function. "O-20110704 -2" which is "O" for order (there are other types) "20110704" is for July 4, 2011 '2' the second order of the day for July 4, 2011 I of course can get the type and date. What I don't know is how to get is the last number. It would seem to be that I would need a loop to determine if the next number existed. LOOP --Check to see if the string exist in a table -- count = count +1 -- until I don't find the string END LOOP; but then I thought I could do something like for $1 in (select string from sometable) LOOP count = count + 1 or something like this for i in 1..999 LOOP -- check for the existence of the string in a table using 'i' -- there will never be 999 orders in one day. END LOOP So here is the question what would be the best way for a multi-user system? If someone has a better thought - it would be helpful. BTW I did NOT design the number - in fact it seems silly to me. Johnf -- 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] interesting sequence
On Tuesday, July 05, 2011 01:11:11 pm Kevin Crain wrote: > You don't need a loop there. Assuming your order id field is of type > varchar you can just build the first part of your string and then do a > count to get the last part using a LIKE comparison: > > select count(id_order) + 1 from sometable WHERE id_order LIKE 'O-20110704 > -%'; > > If you do this inside a function it will be like running it in a > transaction so you shouldn't have to worry about it being a multi-user > system. > > > I like this - looks better than what I'm currently doing. Thanks Johnf -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] using the aggregate function max()
Hi, I need a little help understanding how to attack this problem. I need to find the max(date) of a field but I need that value later in my query. If I select max(x.date_field) as special_date from (select date_field) from table where ...)x I get one row and column. But now I want to use that field in the rest of the query select y.*, max(x.date_field) as special_date from (select date_field) from table where ...)x from aTable y where y.somefield = special_date. The above only returns one row and one column the "special_date." How can I use the aggregate field "special_date" in the rest of the query? Or is there some other way? Johnf -- 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] using the aggregate function max()
On Thursday, September 22, 2011 08:14:58 pm David Johnston wrote: > On Sep 22, 2011, at 22:49, John Fabiani wrote: > > Hi, > > I need a little help understanding how to attack this problem. > > > > I need to find the max(date) of a field but I need that value later in my > > query. > > > > If I > > select max(x.date_field) as special_date from (select date_field) from > > table where ...)x > > > > I get one row and column. > > > > But now I want to use that field in the rest of the query > > > > select y.*, max(x.date_field) as special_date from (select date_field) > > from table where ...)x > > from aTable y where y.somefield = special_date. > > > > The above only returns one row and one column the "special_date." > > > > How can I use the aggregate field "special_date" in the rest of the > > query? Or is there some other way? > > > > Johnf > > Your query above is syntactically invalid. Try this. > > WITH max_date AS ( select max(datefield) AS specialdate from ...) > SELECT * > FROM table > JOIN max_date ON table.somefield = max_date.specialdate; > > You can use a online query instead of the WITH if desired, same effect. > > You could also drop the join and use the max_date CTE in a WHERE clause: > > ... FROM table WHERE table.somefield = (SELECT specialdate FROM max_date) > > David J thanks - I'll look into the "with" Johnf -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] the use of $$string$$
Hi, I just discovered that I can use $$string$$ to account for the problem of single quotes in the string (or other strange char's). However, I noticed that the table field contained E'string'. I actually tried to find info on this but I did not find anything. Could someone explain what it means or better provide a web link for me to discover the info. Thanks in advance, Johnf -- 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] the use of $$string$$
On Friday, November 04, 2011 07:38:29 am John Fabiani wrote: > Hi, > I just discovered that I can use $$string$$ to account for the problem of > single quotes in the string (or other strange char's). However, I noticed > that the table field contained E'string'. I actually tried to find info on > this but I did not find anything. > > Could someone explain what it means or better provide a web link for me to > discover the info. > > Thanks in advance, > Johnf Thank you both for the quick replies and the links. What I still do not understand (I'm a little slow) is the fact that pgadmin3 showed the data as E'string'. So I'm wondering if the data is now different in the sense that I need use the 'E' in the field. For example I have the last name of O'Reilly the field lname now appears to contain E'O'Reilly' So will the normal select statements work; Select * from table where lname = 'O'' Reilly' Select * from table where lname like 'O%' or will I need to know the data contains the "E" and add the 'E' to the sql statements? Thanks Johnf -- 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] the use of $$string$$
On Friday, November 04, 2011 09:05:19 am David Johnston wrote: > On Nov 4, 2011, at 11:26, John Fabiani wrote: > > On Friday, November 04, 2011 07:38:29 am John Fabiani wrote: > >> Hi, > >> I just discovered that I can use $$string$$ to account for the problem > >> of single quotes in the string (or other strange char's). However, I > >> noticed that the table field contained E'string'. I actually tried to > >> find info on this but I did not find anything. > >> > >> Could someone explain what it means or better provide a web link for me > >> to discover the info. > >> > >> Thanks in advance, > >> Johnf > > > > Thank you both for the quick replies and the links. > > > > What I still do not understand (I'm a little slow) is the fact that > > pgadmin3 showed the data as E'string'. So I'm wondering if the data is > > now different in the sense that I need use the 'E' in the field. > > > > For example I have the last name of O'Reilly > > the field lname now appears to contain E'O'Reilly' > > > > So will the normal select statements work; > > > > Select * from table where lname = 'O'' Reilly' > > Select * from table where lname like 'O%' > > > > or will I need to know the data contains the "E" and add the 'E' to the > > sql statements? > > > > Thanks > > Johnf > > Try it and see. > > If, indeed, the E is part of the data you should fix your process. Done > correctly there should be no difference in the end result regardless of > how you choose to identify your strings. > > Don't use pgadmin3 myself so I don't know if what you are looking at would > include the E. If it is outputting DDL (I.e., INSERT statements) it might > but simple SELECT results should not. > > David J. I am testing - but I lack the data to do so. When I discovered the 'E' I quickly decided against using the $$ to insert data. But I did use it with one field and when I do a select from python I do get the 'E' on the only field I have. That was disappointing. I was hoping to short cut the testing and get good advise from the group. I really need to look into this much further but I also have to get the job done. Johnf -- 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] the use of $$string$$
On Friday, November 04, 2011 11:06:37 am Richard Huxton wrote:
> On 04/11/11 15:26, John Fabiani wrote:
> > On Friday, November 04, 2011 07:38:29 am John Fabiani wrote:
> >> Hi,
> >> I just discovered that I can use $$string$$ to account for the problem
> >> of single quotes in the string (or other strange char's). However, I
> >> noticed that the table field contained E'string'. I actually tried to
> >> find info on this but I did not find anything.
> >>
> >> Could someone explain what it means or better provide a web link for me
> >> to discover the info.
> >
> > Thank you both for the quick replies and the links.
> >
> > What I still do not understand (I'm a little slow) is the fact that
> > pgadmin3 showed the data as E'string'. So I'm wondering if the data is
> > now different in the sense that I need use the 'E' in the field.
>
> I think you're missing some bit of the puzzle here.
>
> There's the universal problem of how to deal with quote marks inside
> quotes. The SQL standard says you double the quote.
>
> SELECT length('ab''de');
> length
>
>5
>
> Traditionally, PostgreSQL let you use backslash escapes too, not just
> for single quotes but for some other common C-style idioms.
>
> SELECT length(E'ab\'de');
> length
>
>5
>
> For the last few years, this has been moved into its own quoting style
> so standard strings are always well, standard.
>
> SELECT length('ab\nde');
> length
>
>6
>
> SELECT length(E'ab\nde');
> length
>
>5
>
> The [E'] is an opening quote - both characters. It isn't part of the
> value at all. If a field contains "E'" then you put it there, possibly
> by quoting something in pgadmin that was already quoted.
>
> Likewise you can use $$..$$ to quote strings (actually $$whatever$$).
> That gives you sql-standard escaping apart from single quotes. It's
> especially useful for function bodies which tend to contain their own
> string literals.
>
> SELECT length($$ab\nde$$);
> length
>
>6
>
> None of this should matter from an application however, since its
> database access library should do all the quoting for you.
>
> HTH
thanks for the reply.
I'm using psycopg2.
This is what I'm doing from python
myvarString = "long string that contains single quotes"
cusor.execute("insert into table (pkid, myfield) values (%s, $$%s$$)",(123,
myvarString))
When I execute the above I'm seeing:
E'long string that contains single quotes' in the field. When I do a "select
* from table" I get E'long string that contains single quotes'.
If I do
myvarString = "long string that without single quotes"
cusor.execute("insert into table (pkid, myfield) values (%s, %s)",(123,
myvarString))
I get the following:
"long string that without single quotes"
I have to be doing something wrong. But where?
Johnf
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] updating a sequence
Hi, I have need of a statement that updates the sequence but uses a max() to find the number. alter sequence somename restart with (select max(pk) from sometable). I need this for automating an ETL (using pentaho). Postgres 8.4 Thanks in advance, Johnf -- 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] updating a sequence
On Tuesday, November 15, 2011 08:33:54 am Richard Broersma wrote: > On Tue, Nov 15, 2011 at 8:06 AM, John Fabiani wrote: > > alter sequence somename restart with (select max(pk) from sometable). > > > > I need this for automating an ETL (using pentaho). > > http://www.postgresql.org/docs/9.1/interactive/functions-sequence.html#FUNC > TIONS-SEQUENCE-TABLE I don't see how that helps answer my problem. I know how to update a sequence. I want to pass a value for the restart vaue that depends on a query - all in one statement. I would think it is a common problem i.e. migrating data. Thanks, Johnf -- 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] updating a sequence
On Tuesday, November 15, 2011 07:46:19 pm Scott Marlowe wrote:
> On Tue, Nov 15, 2011 at 5:33 PM, Samuel Gendler
>
> wrote:
> > On Tue, Nov 15, 2011 at 4:28 PM, John Fabiani
wrote:
> >> On Tuesday, November 15, 2011 08:33:54 am Richard Broersma wrote:
> >> > On Tue, Nov 15, 2011 at 8:06 AM, John Fabiani
> >> >
> >> > wrote:
> >> > > alter sequence somename restart with (select max(pk) from
> >> > > sometable).
> >> > >
> >> > > I need this for automating an ETL (using pentaho).
> >> >
> >> > http://www.postgresql.org/docs/9.1/interactive/functions-sequence.html
> >> > #FUNC TIONS-SEQUENCE-TABLE
> >>
> >> I don't see how that helps answer my problem. I know how to update a
> >> sequence. I want to pass a value for the restart vaue that depends on a
> >> query
> >> - all in one statement. I would think it is a common problem i.e.
> >> migrating
> >> data.
> >
> > use a subquery to set the value -
> > select setval('foo', select max(some_id) from some_table)
> > It's all right there in the docs that you were pointed to. We try to
> > encourage people to be somewhat self sufficient around here.
>
> You need to wrap a subselect in ():
>
> select setval('foo', (select max(some_id) from some_table));
>
> That works in 9.1.1. No clue about previous versions off the top of
> my head, but I seem to recall it doesn't work in 8.3 and prior
> versions.
Thanks that did work with the parens.
I could not get to work earlier.
Johnf
--
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] updating a sequence
On Tuesday, November 15, 2011 08:06:30 am John Fabiani wrote: > Hi, > I have need of a statement that updates the sequence but uses a max() to > find the number. > > alter sequence somename restart with (select max(pk) from sometable). > > I need this for automating an ETL (using pentaho). > > Postgres 8.4 > > Thanks in advance, > Johnf Thanks everyone! Johnf -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] using a generated series in function
Hi,
I am attempting (without success) use the generated series of dates that come
from:
select (cast(date_trunc('week', '2011-11-20'::date ) as date) + (i+6)) as
week_date from generate_series(0,84,7) i
in a function.
select function_name(integer, date); -- function returns a numeric
This does NOT work:
select (function_name(303, week_date::date)) as week_date where week_date in
(select (cast(date_trunc('week', '2011-11-20'::date ) as date) + (i+6)) as
week_date from generate_series(0,84,7) i )
The error is:
ERROR: column "week_date" does not exist
LINE 1: select (xchromasun._chromasun_getqtyordered(303, week_date::...
I hope I can do this? What am I doing wrong?
Johnf
--
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] using a generated series in function
Actually what would the "from" be - this could be a newbie issue here?
Neither statement requires a "from" because neither of the statements uses a
table - I think! I'll try to add one but the first part is a function like a
any other function. What is the "from" when you do:
"select now()" - really I don't know!
The second part is tricky because I don't really understand it. Howerver, I
have used it several times (got it off the web somewhere) but only in a "for
loop". If I just run it by it's self it generates a table of dates.
Therefore, I have always thought of it as a function. Again, like "select
now()"
So I know this must sound like I'm sort of idiot - just never considered the
second half (the part that provides the dates) anything other than a postgres
function.
Johnf
On Friday, December 16, 2011 01:30:53 AM Misa Simic wrote:
> It is not totally clear to me what are u trying to do... But in second
> query it seems there is missing "from"
>
> It is as
>
> SELECT week-date::date AS week-date WHERE week-date in (subquery which
> have from)
>
> So week-date column in main query does not exist..
>
> Sent from my Windows Phone From: John Fabiani
> Sent: 16 December 2011 05:16
> To: pgsql-sql@postgresql.org
> Subject: [SQL] using a generated series in function
> Hi,
>
> I am attempting (without success) use the generated series of dates that
> come from:
> select (cast(date_trunc('week', '2011-11-20'::date ) as date) + (i+6)) as
> week_date from generate_series(0,84,7) i
>
> in a function.
> select function_name(integer, date); -- function returns a numeric
>
> This does NOT work:
> select (function_name(303, week_date::date)) as week_date where week_date in
> (select (cast(date_trunc('week', '2011-11-20'::date ) as date) + (i+6)) as
> week_date from generate_series(0,84,7) i )
>
> The error is:
> ERROR: column "week_date" does not exist
> LINE 1: select (xchromasun._chromasun_getqtyordered(303, week_date::...
>
> I hope I can do this? What am I doing wrong?
> Johnf
--
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] using a generated series in function
I have solved my problem. But this still does not explain the idea of
"from"
select foo.week_date, xchromasun._chromasun_getqtyordered(303, foo.week_date)
as week_qty from
(select ((date_trunc('week', '2011-11-20'::date )::date) + (i+6)) as
week_date from generate_series(0,84,7)
i ) as foo
The above works!
Johnf
On Friday, December 16, 2011 02:46:18 AM John Fabiani wrote:
> Actually what would the "from" be - this could be a newbie issue here?
> Neither statement requires a "from" because neither of the statements uses a
> table - I think! I'll try to add one but the first part is a function like
> a any other function. What is the "from" when you do:
> "select now()" - really I don't know!
>
> The second part is tricky because I don't really understand it. Howerver, I
> have used it several times (got it off the web somewhere) but only in a
> "for loop". If I just run it by it's self it generates a table of dates.
> Therefore, I have always thought of it as a function. Again, like "select
> now()"
>
> So I know this must sound like I'm sort of idiot - just never considered the
> second half (the part that provides the dates) anything other than a
> postgres function.
>
> Johnf
>
> On Friday, December 16, 2011 01:30:53 AM Misa Simic wrote:
> > It is not totally clear to me what are u trying to do... But in second
> > query it seems there is missing "from"
> >
> > It is as
> >
> > SELECT week-date::date AS week-date WHERE week-date in (subquery which
> > have from)
> >
> > So week-date column in main query does not exist..
> >
> > Sent from my Windows Phone From: John Fabiani
> > Sent: 16 December 2011 05:16
> > To: pgsql-sql@postgresql.org
> > Subject: [SQL] using a generated series in function
> > Hi,
> >
> > I am attempting (without success) use the generated series of dates that
> > come from:
> > select (cast(date_trunc('week', '2011-11-20'::date ) as date) + (i+6))
> > as
> > week_date from generate_series(0,84,7) i
> >
> > in a function.
> > select function_name(integer, date); -- function returns a numeric
> >
> > This does NOT work:
> > select (function_name(303, week_date::date)) as week_date where
> > week_date in (select (cast(date_trunc('week', '2011-11-20'::date ) as
> > date) + (i+6)) as week_date from generate_series(0,84,7) i )
> >
> > The error is:
> > ERROR: column "week_date" does not exist
> > LINE 1: select (xchromasun._chromasun_getqtyordered(303, week_date::...
> >
> > I hope I can do this? What am I doing wrong?
> > Johnf
--
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] using a generated series in function
As always I respect your insights - Adrian. I do understand what I did wrong
in my first attempt at getting my statement to work. But it is either over my
head or there is something missing. Where is the "from" in
select now()?
I have been using similar SQL statements for years. I never questioned why
there was not a 'from' until this list noted that I was missing a 'From'. I
then went to the postgres site to read. That's how I determined what I had
done incorrectly.
I hope this is not one of those things like javascript where all white space
is ignored unless it's not! I hate that language! It appears that everything
needs a 'From' in SQL (reading the doc's) and the above statement is missing a
'From'!
As always everyone - thanks for your help!
Johnf
On Friday, December 16, 2011 07:31:40 AM Adrian Klaver wrote:
> FROM Clause
> "select
>
> A sub-SELECT can appear in the FROM clause. This acts as though its
> output were created as a temporary table for the duration of this single
> SELECT command. Note that the sub-SELECT must be surrounded by parentheses,
> and an alias must be provided for it. A VALUES command can also be used
> here. "
>
> >
> >
> > select foo.week_date, xchromasun._chromasun_getqtyordered(303,
> > foo.week_date) as week_qty from
> >
> > (select ((date_trunc('week', '2011-11-20'::date )::date) + (i+6)) as
> >
> > week_date from generate_series(0,84,7)
> >
> > i ) as foo
> >
> >
> > The above works!
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] avoid the creating the type for setof
Hi, I recall somewhere I saw a simple plpgsql function that returned a table with more than one record that did not use a defined type or a temp table ( at least I think I did). Is it possible to create such a function that will return more than one record and not require a record type or temp table? something like return setof record as return query select ... return Johnf -- 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] avoid the creating the type for setof
On Friday, December 30, 2011 06:26:19 AM John Fabiani wrote: > Hi, > I recall somewhere I saw a simple plpgsql function that returned a table > with more than one record that did not use a defined type or a temp table ( > at least I think I did). Is it possible to create such a function that > will return more than one record and not require a record type or temp > table? > > something like > > return setof record as > return query select ... > > return > > > Johnf Thanks all - I knew I had seen it in the past. Johnf -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] foreign key is it a real key
Hi, I have read a few articles and I'm not sure if it's me or the authors but I do not believe my question was answered. If I have table that has a PK and a FK - will the planner use the FK just same as it would use the PK? IOW's is a FK also an index used by the planner? I have a lagacy table that contains a FK constraint (vendor_id --> vendor_info). I believe I need an index to improve the performance of a query because I'm using the column as part of my join. Explain does not appear to use it as an index (instead of a seq scan it uses a hash join). But the table all ready has a FK key contraint on the column. Thanks in advance, Johnf -- 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] foreign key is it a real key
On Monday, February 13, 2012 04:50:47 PM Andreas Kretschmer wrote: > John Fabiani wrote: > > Hi, > > I have read a few articles and I'm not sure if it's me or the authors > > but I do not believe my question was answered. > > > > If I have table that has a PK and a FK - will the planner use the FK > > just same as it would use the PK? IOW's is a FK also an index used by > > the planner? > > > > I have a lagacy table that contains a FK constraint (vendor_id --> > > vendor_info). I believe I need an index to improve the performance of a > > query because I'm using the column as part of my join. Explain does > > not appear to use it as an index (instead of a seq scan it uses a hash > > join). But the table all ready has a FK key contraint on the column. > > Yes, for large tables you have to create a own index on your fk-column. > > > Andreas Thanks Johnf -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] crosstab help
I have a simple table
item_number week_of planned_qoh
-- -- --
5 2012-02-05 30
5 2012-02-12 40
5 2012-02-19 50
where
item_number text
week_of date
planned_qoh integer
I have a function that returns the table as above:
chromasun._chromasun_totals(now()::date)
I want to see
5 2012-02-05 2012-02-122012-02-19
30 40 50
This is what I have tried (although, I have tired many others)
select * from crosstab('select item_number::text as row_name,
to_char(week_of,''MM-DD-YY'') as bucket, planned_qoh::integer as buckvalue
from xchromasun._chromasun_totals(now()::date)')
as ct(item_number text, week_of date, planned_qoh integer)
I get
ERROR: return and sql tuple descriptions are incompatible
What am I doing wrong?
Johnf
--
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] crosstab help
That worked! However, I need the actual date to be the column heading? And
of course the dates change depending on the date passed to the function:
xchromasun._chromasun_totals(now()::date)
So how do I get the actual dates as the column header?
johnf
On Friday, February 24, 2012 09:27:38 AM Andreas Gaab wrote:
> Hi,
>
> the return type of the crosstab must be defined correctly, according to the
> number of expected columns.
>
> Try following (untested):
>
> select * from crosstab(
> 'select item_number::text as row_name, to_char(week_of,''MM-DD-YY'')::date
> as bucket, planned_qoh::integer as buckvalue from
> xchromasun._chromasun_totals(now()::date)') as ct(item_number text,
> week_of_1 date, week_of_2 date, week_of_3 date)
>
> Regards,
> Andreas
>
>
>
> -Ursprüngliche Nachricht-
> Von: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org]
> Im Auftrag von John Fabiani Gesendet: Freitag, 24. Februar 2012 09:11
> An: pgsql-sql@postgresql.org
> Betreff: [SQL] crosstab help
>
> I have a simple table
> item_number week_of planned_qoh
> -- -- --
> 5 2012-02-05 30
> 5 2012-02-12 40
> 5 2012-02-19 50
>
>
> where
> item_number text
> week_of date
> planned_qoh integer
>
> I have a function that returns the table as above:
>
> chromasun._chromasun_totals(now()::date)
>
> I want to see
>
> 5 2012-02-05 2012-02-122012-02-19
> 30 40 50
>
> This is what I have tried (although, I have tired many others)
>
> select * from crosstab('select item_number::text as row_name,
> to_char(week_of,''MM-DD-YY'') as bucket, planned_qoh::integer as buckvalue
> from xchromasun._chromasun_totals(now()::date)') as ct(item_number text,
> week_of date, planned_qoh integer)
>
> I get
> ERROR: return and sql tuple descriptions are incompatible
>
> What am I doing wrong?
>
> Johnf
>
> --
> 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
Re: [SQL] crosstab help
Thanks for the insight!
johnf
On Friday, February 24, 2012 09:48:03 AM Andreas Gaab wrote:
> As far as I know you must define the numbers (and types) of columns and
> column headers individually for each query or define some custom
> function...
>
> Andreas
>
> -Ursprüngliche Nachricht-
> Von: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org]
> Im Auftrag von John Fabiani Gesendet: Freitag, 24. Februar 2012 09:39
> An: pgsql-sql@postgresql.org
> Betreff: Re: [SQL] crosstab help
>
> That worked! However, I need the actual date to be the column heading?
> And of course the dates change depending on the date passed to the
> function: xchromasun._chromasun_totals(now()::date)
>
> So how do I get the actual dates as the column header?
> johnf
>
> On Friday, February 24, 2012 09:27:38 AM Andreas Gaab wrote:
> > Hi,
> >
> > the return type of the crosstab must be defined correctly, according
> > to the number of expected columns.
> >
> > Try following (untested):
> >
> > select * from crosstab(
> > 'select item_number::text as row_name,
> > to_char(week_of,''MM-DD-YY'')::date
> > as bucket, planned_qoh::integer as buckvalue from
> > xchromasun._chromasun_totals(now()::date)') as ct(item_number text,
> > week_of_1 date, week_of_2 date, week_of_3 date)
> >
> > Regards,
> > Andreas
> >
> >
> >
> > -Ursprüngliche Nachricht-
> > Von: pgsql-sql-ow...@postgresql.org
> > [mailto:pgsql-sql-ow...@postgresql.org]
> > Im Auftrag von John Fabiani Gesendet: Freitag, 24. Februar 2012 09:11
> > An: pgsql-sql@postgresql.org
> > Betreff: [SQL] crosstab help
> >
> > I have a simple table
> > item_number week_of planned_qoh
> > -- -- --
> > 5 2012-02-05 30
> > 5 2012-02-12 40
> > 5 2012-02-19 50
> >
> >
> > where
> > item_number text
> > week_of date
> > planned_qoh integer
> >
> > I have a function that returns the table as above:
> >
> > chromasun._chromasun_totals(now()::date)
> >
> > I want to see
> >
> > 5 2012-02-05 2012-02-122012-02-19
> >
> > 30 40
> > 50
> >
> > This is what I have tried (although, I have tired many others)
> >
> > select * from crosstab('select item_number::text as row_name,
> > to_char(week_of,''MM-DD-YY'') as bucket, planned_qoh::integer as
> > buckvalue from xchromasun._chromasun_totals(now()::date)') as
> > ct(item_number text, week_of date, planned_qoh integer)
> >
> > I get
> > ERROR: return and sql tuple descriptions are incompatible
> >
> > What am I doing wrong?
> >
> > Johnf
> >
> > --
> > 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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] crosstab maybe by case statement
Hi Folks,
I have one that I need your advise on how to construct.
I have the need to create a series of dates starting from a Sunday - one week
apart.
(select ((date_trunc('week', '2012-02-12'::date)::date) +(i+6)) as week_date
from
generate_series(0,84,7) i) as foo
that gives a table of sunday dates starting from '2012-02-12'::date
The dates will be used in the column headings ( I don't know in advance what
those dates will be - because the start sunday can change).
I have written functions to return sum()'s of a field for the week starting
from the sundays returned from the above dates.
getqtyordered(itemsite_id, foo.week_date) -- returns qty order for the week
getqtyalloc(itemsite_id, foo.week_date) -- qty_alloc for the week
... I actually have several of these types of functions.
And of course there is other information retrieved.
What I need to output is the following:
Namedate 1 date2 date3date4 ...
qty order 10820 15
qty_alloc 6 03 50
What I can't figure out is how to get the dates to become my field/column
name.
BELOW IS MY ACTUAL FUNCTION:
CREATE OR REPLACE FUNCTION xchromasun._chromasun_totals(date)
RETURNS SETOF weekly_mpr AS
$BODY$
DECLARE
_passed_date alias for $1;
_start_date date;
_warehous_id integer;
_firm_planned_accum numeric;
_running_avail numeric;
_start_qoh numeric;
_itemsite_id integer;
_olditemsite_id integer;
rec record;
myrecord weekly_mpr;
counter integer;
BEGIN
_running_avail :=0;
_firm_planned_accum := 0;
_olditemsite_id := 0;
_itemsite_id := 0;
counter := 0;
--find Sunday
select the_date from
(select (_passed_date::date) - num as the_date, extract(dow from
((_passed_date::date) - num)) as weekday
from (select generate_series(0,6) as num) as t) as myans where weekday = 0
into _start_date;
for rec in select itemsite_id,item_number, item_descrip1, itemsite_qtyonhand,
itemsite_safetystock,foo.week_date,itemsite_leadtime,warehous_code,
coalesce(vend_name,'NOT ON FILE') as "vendor", coalesce(vend_number, 'NONE')
as "vend_number",
xchromasun._chromasun_getqtyordered(itemsite_id, foo.week_date) as
"qty_ordered",
xchromasun._chromasun_getqtyallocated(itemsite_id, foo.week_date) as
"qty_alloc",
xchromasun._chromasun_getqtypr(itemsite_id, foo.week_date) as "purch_req",
xchromasun._chromasun_getqtyplanneddemand(itemsite_id, foo.week_date) as
"planned_demand",
qtyavailable(itemsite_id, foo.week_date) as "qty_avail",
(select xchromasun._chromasun_getqtyfirmed(itemsite_id, foo.week_date)) as
"firm_planned_orders",
(xchromasun._chromasun_getqtyplanned(itemsite_id, foo.week_date) - (select
xchromasun._chromasun_getqtyfirmed(itemsite_id, foo.week_date))) as
"planned_orders"
from public.itemsite
left join whsinfo on (warehous_id = itemsite_warehous_id)
left join item on (item_id = itemsite_item_id)
left join itemsrc on (itemsrc_item_id = item_id)
left outer join vendinfo on (vend_id = itemsrc_vend_id),
(select ((date_trunc('week', _start_date::date)::date) +(i+6)) as week_date
from
generate_series(0,84,7) i) as foo
where itemsite_item_id in (select item_id from item) and item_type = 'P'
order by item_number asc,vend_number,week_date
loop
counter := counter +1;
_olditemsite_id :=rec.itemsite_id;
IF _itemsite_id <> _olditemsite_id THEN
_itemsite_id := rec.itemsite_id;
_running_avail := rec.itemsite_qtyonhand;
END IF;
_firm_planned_accum = _firm_planned_accum + rec.firm_planned_orders ;
_running_avail = _running_avail - rec.planned_demand + rec.qty_ordered -
rec.qty_alloc ;
myrecord.counter := counter::integer;
myrecord.warehous_code := rec.warehous_code;
myrecord.week_of := rec.week_date;
myrecord.qty_ordered := rec.qty_ordered;
myrecord.firm_planned_orders := rec.firm_planned_orders;
myrecord.planned_orders := rec.planned_orders;
myrecord.item_number := rec.item_number;
myrecord.item_descrip1 := rec.item_descrip1;
myrecord.itemsite_qtyonhand := rec.itemsite_qtyonhand;
myrecord.itemsite_safetystock := rec.itemsite_safetystock;
myrecord.qty_alloc := rec.qty_alloc;
myrecord.qty_avail := rec.qty_avail;
myrecord.planned_qoh := _running_avail;
myrecord.firm_avail := _firm_planned_accum;
myrecord.lead_time := rec.itemsite_leadtime;
myrecord.vend_number := rec.vend_number;
myrecord.vendor := rec.vendor;
myrecord.purch_req := rec.purch_req;
myrecord.planned_demand := -rec.planned_demand;
return next myrecord;
end loop;
return;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
ROWS 1000;
ALTER FUNCTION xchromasun._chromasun_totals(date)
OWNER TO postgres;
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] crosstab
Hi, I don't know if it because I'm as sick as dog or I'm just a plain idiot - most likely a little of both. Here is my table week_of date, item_number text, planned_demand integer. I have week_ofitem_number planned 2012-02-125200 2012-02-195-30 2012-02-265-16 I want to see item_number 2012-02-122012-02-19 2012-02-26 5 200 -30 -16 I actually have added fields but that should get me started. Thanks for help from an old man with a very bad cold. Johnf -- 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] crosstab
select *
from crosstab('select item_number::text, week_of::date, planned_demand::text
from holding_table order by 1,2')
as ct(row_name text, week_of date, planned text)
The above does not work. What am I doing wrong?
Johnf
On Monday, March 12, 2012 06:01:21 PM Pavel Stehule wrote:
> Hello
>
> maybe this article helps
> http://stackoverflow.com/questions/3002499/postgresql-crosstab-query
>
> there are more ways
>
> Regards
>
> Pavel Stehule
>
> 2012/3/12 John Fabiani :
> > Hi,
> > I don't know if it because I'm as sick as dog or I'm just a plain idiot
> > - most likely a little of both.
> >
> > Here is my table
> >
> > week_of date,
> > item_number text,
> > planned_demand integer.
> >
> > I have
> > week_ofitem_number planned
> > 2012-02-125200
> > 2012-02-195-30
> > 2012-02-265-16
> >
> > I want to see
> >
> > item_number 2012-02-122012-02-19 2012-02-26
> > 5 200 -30 -16
> >
> > I actually have added fields but that should get me started.
> >
> > Thanks for help from an old man with a very bad cold.
> >
> > Johnf
> >
> > --
> > 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
[SQL] Can I read the data without commit
Hi, I know this is a newbie question but I have never had the need to do the following. I start a transaction. Begin Then I insert a lot of data - let's say two hundred rows. Now I need to read the same data (so the user can review). If the user thinks all is right then commit. Can I read the data I just inserted without committing? Johnf -- 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] Can I read the data without commit
Yes I understand - but I want to know is it possible? Can I read the data I just inserted without a commit. Johnf On Friday, March 23, 2012 03:46:10 PM Rob Sargent wrote: > If possible have the review done before starting the transaction. No > sense in holding on to that stuff too long. Potential concurrency issues > etc. > > On 03/23/2012 03:40 PM, Jonathan S. Katz wrote: > > On Mar 23, 2012, at 5:33 PM, John Fabiani wrote: > >> I start a transaction. > >> Begin > >> > >> Then I insert a lot of data - let's say two hundred rows. > >> > >> Now I need to read the same data (so the user can review). > >> > >> If the user thinks all is right then > >> commit. > >> > >> Can I read the data I just inserted without committing? > > > > Yes, as long as you run your SELECT within the transaction that you > > started. > > > > Jonathan -- 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] Can I read the data without commit
On Friday, March 23, 2012 04:00:56 PM Rob Sargent wrote: > And I believe Jonathon confirmed that you could, with the caveat that > you must select from within the transaction. I don't see that you've > laid that out your connection stategy so ymmv. I didn't see Jonathon response? Must be a direct email to me and not to the list. But thanks that helps. Johnf -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] getting the OS user name
Hi, In my app it is possible to login as one name and use a different name to login to postgres. Is it possible to get the actual OS login name using plsql. Johnf -- 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] getting the OS user name
On Monday, April 23, 2012 04:52:25 PM Andreas Kretschmer wrote: > John Fabiani wrote: > > Hi, > > In my app it is possible to login as one name and use a different name > > to > > login to postgres. > > > > Is it possible to get the actual OS login name using plsql. > > Since you can login to an remote database server: no. > > > Andreas Ahhh! never considered that issue. Thanks Johnf -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] syncing - between databases
I need to maintain a sync-ed table across several databases. For example I have a customer table in 5 databases. If a user of any of the databases inserts a new customer I need to insert the new record into the other four databases. But question is updates and deletes. I can use a trigger and dblink to update the other databases when the action is an insert because in each of the other databases I don't have to worry about a locked record. But what happens if a user is updating at the same moment as a different user in a different database is updating the same customer. Can a race condition occur? I was thinking I could create a master database. And have all the other databases use dblink to excute the master trigger. Any advise would be helpful, Johnf -- 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] syncing - between databases
All on the same cluster (only one server). Although, it would be nice to have only one table there are real business reasons to dup the databases. I am interested in how you dealt with a queue table. Would you take a little time to describe the way it worked. Johnf On Saturday, May 12, 2012 08:53:52 PM you wrote: > Are these 5 databases on different servers and at different locations or > are they on the same local cluster? > If they are all on the same local cluster you may want to rethink how you > are storing customer data. The design you describe seems redundant. > > If you are dealing with multiple servers (and perhaps business rules that > require duplicate, writable user tables at each location?) then your plan > needs to account for network failure. A synchronous cross-network dblink > trigger mechanism left to its own devices will eventually fail and you will > be left with inconsistent data. Nothing wrong with dblink but you need to > build in some error handling. > > I've built systems that accomplished similar things by writing data to a > queue table (in addition to your local master customer table) which is then > reconciled/synced out to other nodes or process by an periodic script that > is able to deal with or alert on locking/dupe key/network and other errors > that keep it from properly syncing a row to all other nodes. This > introduces added durability to your sync mechanism but also introduces some > lag time. Pick your poison. > > -steve > > On Sat, May 12, 2012 at 7:28 AM, John Fabiani wrote: > > I need to maintain a sync-ed table across several databases. For > > example I have a customer table in 5 databases. If a user of any of > > the databases inserts a new customer I need to insert the new record > > into the other four databases. But question is updates and deletes. > > > > I can use a trigger and dblink to update the other databases when the > > action > > is an insert because in each of the other databases I don't have to > > worry > > about a locked record. But what happens if a user is updating at the > > same moment as a different user in a different database is updating the > > same customer. Can a race condition occur? > > > > I was thinking I could create a master database. And have all the other > > databases use dblink to excute the master trigger. > > > > > > Any advise would be helpful, > > > > Johnf > > > > -- > > 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
[SQL] defaults in a function
Hi, In python when I create a method/function is set a default value for a passed value if one is not provided. def foo(self, event = None): In the above function if the second value is not passed a value of None is used as the default. Is this possible with plpgsql??? Johnf -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] using ordinal_position
I'm attempting to retrieve data using a select statement without knowing the column names. I know the ordinal position but not the name of the column (happens to be a date::text and I have 13 fields). Below provides the name of the column in position 3: select column_name from (select column_name::text, ordinal_position from information_schema.columns where table_name='wk_test') as foo where ordinal_position = 3; But how can I use the above as a column name in a normal select statement. Unlike other databases I just can't use ordinal position in the select statement - RIGHT??? Johnf -- 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] using ordinal_position
On 06/11/2012 06:53 AM, Igor Neyman wrote: -Original Message- From: John Fabiani [mailto:jo...@jfcomputer.com] Sent: Thursday, June 07, 2012 7:18 PM To: pgsql-sql@postgresql.org Subject: using ordinal_position I'm attempting to retrieve data using a select statement without knowing the column names. I know the ordinal position but not the name of the column (happens to be a date::text and I have 13 fields). Below provides the name of the column in position 3: select column_name from (select column_name::text, ordinal_position from information_schema.columns where table_name='wk_test') as foo where ordinal_position = 3; But how can I use the above as a column name in a normal select statement. Unlike other databases I just can't use ordinal position in the select statement - RIGHT??? Johnf David gave you already pretty complete answer. I just wonder what are these "other" RDBMSs that allow to use ordinal column positions in a query? I am familiar with a few (in addition to Postgress), and none of them does that, not in "select" list., though everybody allow ordinal position from "select" list in "order by" and "group by". Regards, Igor Neyman VFP uses position (you might not consider DBF a database). MsSQl (ordinal_position). Those are the only two I'm aware of. Johnf -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] how to use schema with data type
I have tried to use a user defined data type I created within a schema. But I could not figure it out. CREATE OR REPLACE FUNCTION xchromasun._chromasun_totals(date) RETURNS SETOF xchromasun.weekly_mpr AS CREATE OR REPLACE FUNCTION xchromasun._chromasun_totals(date) RETURNS SETOF "xchromasun.weekly_mpr" AS I had to move the user defined data type to "public". Could someone explain how I might get that done - that is use a schema data type from a function. I'm using 8.4.x. Johnf -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] strange corruption?
Hi, I have the following statement in a function. UPDATE orderseq SET orderseq_number = (orderseq_number + 1) WHERE (orderseq_name='InvcNumber'); All it does is update a single record by incrementing a value (int). But it never completes. This has to be some sort of bug. Anyone have a thought what would cause this to occur. To my knowledge it was working and does work in other databases. Johnf -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
