Re: [SQL] SELECT syntax synopsis: column_definition?
Gregory Stark <[EMAIL PROTECTED]> writes:
> I haven't quite figured out how this is useful though. It probably makes more
> sense if you use plpgsql but I still don't quite see what the use case is.
IIRC, the case that actually convinced people to allow it was dblink.
You want to be able to do something like
select *
from dblink('select a,b,c from remote_table') as (a int, b text, c float8);
The declaration of dblink can't be any more specific than "RETURNS SETOF
RECORD", so there's no help to be had there. The only way to explain to
the parser what your dblink call is going to return is something like
the above. And the parser does need to know it, so it knows what to
expand "*" to (or more generally, to do things like joins involving the
rowset result).
regards, tom lane
---(end of broadcast)---
TIP 6: explain analyze is your friend
[SQL] raw data into table process
I am trying to record the following entries into a table. I'm curious
to know if there's an efficient/effective way of doing this? This
entries come from an ancient datalogger (note: separated by space and
uses YY/MM/DD format to record date)
Plain file sample.dat
3665 OK BS 07/08/16 07:28
3665 CC BS 07/08/16 07:29
3665 CS BS 07/08/16 07:29
3665 CS BS 07/08/16 07:29
4532 OK BS 07/08/16 07:34
4004 OK BS 07/08/16 07:51
3991 OK BS 07/08/16 07:54
This is the table that I'm adding the entries to
CREATE TABLE maintenance
(
maintenance_id SERIAL PRIMARY KEY,
meter_id integer,
status text,
inspector text,
inspection_date timestamp with time zone,
)
-- Begin SQL Script
-- First table to dump the records in
CREATE TABLE dataload1
(data text)
-- Dump records using \copy
\copy dataload1 FROM sample.dat
-- Second table to import unique records ONLY
CREATE TABLE dataload2 AS
SELECT DISTINCT
data FROM dataload1;
-- Now I update unique records into the maintenance table
-- maintenance_id is SERIAL so it will be populated automatically
INSERT INTO maintenance(meter_id, status, inspector, inspection_date)
SELECT substr("data", 1, 4)::int
, substr("data", 8, 3)
, substr("data", 21, 2)
, (20||substr("data", 24, 2) ||'-'|| substr("data", 27, 2) ||'-'||
substr("data", 30, 2)||' '||substr("data", 33, 5))::timestamp as
inspection_date
FROM dataload2
-- So the new records will also be in timestamp order
ORDER BY inspection_date ;
-- Some housekeeping
VACUUM FULL VERBOSE ANALYZE maintenance;
-- Finally, drop the temporary tables
DROP TABLE dataload1
DROP TABLE dataload2
-- End SQL script
Any thoughts and suggestions welcome.
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Re: [SQL] SELECT syntax synopsis: column_definition?
--- Alvaro Herrera <[EMAIL PROTECTED]> wrote: > Any function declared as returning SETOF RECORD needs it, when you don't > use OUT params. Before OUT params existed, it was the only way to use > those functions. Thanks everyone for the exposition! It makes sense. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] SELECT syntax synopsis: column_definition?
Gregory Stark wrote: > "Michael Glaesemann" <[EMAIL PROTECTED]> writes: > > > ERROR: a column definition list is only allowed for functions returning > > "record" > > > > So the *form* is right, but I don't know of an example that works. > > postgres=# create function testf() returns record as 'select 1' language sql; > CREATE FUNCTION > postgres=# select * from testf() as (i integer); > i > --- > 1 > (1 row) > > > I haven't quite figured out how this is useful though. It probably makes more > sense if you use plpgsql but I still don't quite see what the use case is. Any function declared as returning SETOF RECORD needs it, when you don't use OUT params. Before OUT params existed, it was the only way to use those functions. -- Alvaro Herrera Valdivia, Chile ICBM: S 39º 49' 18.1", W 73º 13' 56.4" "The eagle never lost so much time, as when he submitted to learn of the crow." (William Blake) ---(end of broadcast)--- TIP 1: 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] SELECT syntax synopsis: column_definition?
On 8/21/07, Gregory Stark <[EMAIL PROTECTED]> wrote: > "Michael Glaesemann" <[EMAIL PROTECTED]> writes: > > > ERROR: a column definition list is only allowed for functions returning > > "record" > > > > So the *form* is right, but I don't know of an example that works. > > postgres=# create function testf() returns record as 'select 1' language sql; > CREATE FUNCTION > postgres=# select * from testf() as (i integer); > i > --- > 1 > (1 row) > > > I haven't quite figured out how this is useful though. It probably makes more > sense if you use plpgsql but I still don't quite see what the use case is. I use them with the crosstab function in the tablefunc contrib module. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] SELECT syntax synopsis: column_definition?
"Michael Glaesemann" <[EMAIL PROTECTED]> writes: > ERROR: a column definition list is only allowed for functions returning > "record" > > So the *form* is right, but I don't know of an example that works. postgres=# create function testf() returns record as 'select 1' language sql; CREATE FUNCTION postgres=# select * from testf() as (i integer); i --- 1 (1 row) I haven't quite figured out how this is useful though. It probably makes more sense if you use plpgsql but I still don't quite see what the use case is. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 1: 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] SELECT syntax synopsis: column_definition?
On Aug 21, 2007, at 18:04 , Michael Glaesemann wrote:
So the *form* is right, but I don't know of an example that works.
CREATE TABLE foos
(
foo text PRIMARY KEY
, title text NOT NULL
);
INSERT INTO foos (foo, title) values
('foo', 'the great')
, ('bar', 'the extravagant')
, ('baz', 'the indisputable');
CREATE OR REPLACE FUNCTION get_foo() RETURNS record
LANGUAGE plpgsql AS $body$
DECLARE
v_record record;
BEGIN
SELECT INTO v_record
*
FROM foos
ORDER BY RANDOM()
LIMIT 1;
RETURN v_record;
END;
$body$;
a |b
-+--
baz | the indisputable
(1 row)
test=# SELECT *
test-# FROM get_foo() AS (a text, b text);
a |b
-+-
bar | the extravagant
(1 row)
IIRC, this form is used by the crosstab functions in tablefunc.
Michael Glaesemann
grzm seespotcode net
---(end of broadcast)---
TIP 1: 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] SELECT syntax synopsis: column_definition?
On Aug 21, 2007, at 14:34 , Richard Broersma Jr wrote: Can any one give an example of the difference between a column_alias and a column_definition when using a function in the FROM clause? from the manual: http://www.postgresql.org/docs/8.2/interactive/sql-select.html "function_name ( [ argument [, ...] ] ) [ AS ] alias [ ( column_alias [, ...] | column_definition [, ...] ) ]" I believe it's similar to this # select * from generate_series(1,10) as a(s); s 1 2 3 4 5 6 7 8 9 10 (10 rows) But like this: # select * from generate_series(1,10) as a(s text); But not quite, as this raises an error :) ERROR: a column definition list is only allowed for functions returning "record" So the *form* is right, but I don't know of an example that works. You've got me curious now, too! Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Make a SQL statement not run trigger
On 8/21/07, Jon Collette <[EMAIL PROTECTED]> wrote: > I think this will work for what I need. I have been messing around with > this using select into > > /select True as donothing into temporary table table_trigger_name; > then run statement that I want to be ignored > / > The trigger would have a select upon the table_trigger_name to determine > if it should run or not. I am having issues catching the exception when > the table is not found. > Note that if you use temp tables much, you need to make sure that autovacuum is up and running (or vacuum regularly) to keep the system catalogs from getting bloated. Just FYI. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Make a SQL statement not run trigger
I think this will work for what I need. I have been messing around with this using select into /select True as donothing into temporary table table_trigger_name; then run statement that I want to be ignored / The trigger would have a select upon the table_trigger_name to determine if it should run or not. I am having issues catching the exception when the table is not found. This is my test function of my trigger /Create or replace function trigger_test() returns boolean as $$ declare donothing boolean; begin donothing := False; select donothing into donothing from table_trigger_name; return boolean; end; $$ LANGUAGE plpgsql; / Which of course will error when there is no table_trigger_name made for that session. I couldn't find an exception in the exceptions list for table not found errors. Scott Marlowe wrote: On 8/21/07, Jon Collette <[EMAIL PROTECTED]> wrote: Is it possible to run an insert,update, or delete and have it not launch a trigger like it normally would? For example could I set a value DONOTRUN = True; insert into contacts The closest thing to a session variable for pgsql is going to likely be a temp table. you could have a temp table for each session that stores such things and the trigger looks in the temp table to see what to do. Or is there just a global variable I could set to disable triggers and then reset it? And would that be a per connection variable? Yes, but that would open you up to race conditions. If another session wanted the trigger to fire it would not get it. Using a temp table would allow you to get around the race condition. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Make a SQL statement not run trigger
Good Idea. However some of my triggers use the DELETE action as well. So I can't use this method. Thomas Kellerer wrote: Jon Collette wrote on 21.08.2007 23:26: Is it possible to run an insert,update, or delete and have it not launch a trigger like it normally would? For example could I set a value DONOTRUN = True; insert into contacts Where the trigger on contacts would call a function that would have an IF statment for that DONOTRUN value? Or is there just a global variable I could set to disable triggers and then reset it? And would that be a per connection variable? What we have done once, was to include a column in the table for this purpose. If a special value for the column was provided during UPDATE or INSERT, the trigger would immediately terminate, not doing any work. Thus the trigger still "fires" every time, but simply won't do nothing. Not very elegant, but worked for our environment. Thomas ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Make a SQL statement not run trigger
Jon Collette wrote on 21.08.2007 23:26: Is it possible to run an insert,update, or delete and have it not launch a trigger like it normally would? For example could I set a value DONOTRUN = True; insert into contacts Where the trigger on contacts would call a function that would have an IF statment for that DONOTRUN value? Or is there just a global variable I could set to disable triggers and then reset it? And would that be a per connection variable? What we have done once, was to include a column in the table for this purpose. If a special value for the column was provided during UPDATE or INSERT, the trigger would immediately terminate, not doing any work. Thus the trigger still "fires" every time, but simply won't do nothing. Not very elegant, but worked for our environment. Thomas ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Make a SQL statement not run trigger
On 8/21/07, Jon Collette <[EMAIL PROTECTED]> wrote: > Is it possible to run an insert,update, or delete and have it not launch > a trigger like it normally would? > > For example could I set a value > DONOTRUN = True; > insert into contacts The closest thing to a session variable for pgsql is going to likely be a temp table. you could have a temp table for each session that stores such things and the trigger looks in the temp table to see what to do. > Or is there just a global variable I could set to disable triggers and > then reset it? And would that be a per connection variable? Yes, but that would open you up to race conditions. If another session wanted the trigger to fire it would not get it. Using a temp table would allow you to get around the race condition. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Make a SQL statement not run trigger
Thanks for the reply. It looks like this will disable the trigger for all connections to the database. So if the chance occured that another connection was using the table at the same time it wouldn't launch the trigger either? am I wrong? I hope so ;) Rodrigo De León wrote: On 8/21/07, Jon Collette <[EMAIL PROTECTED]> wrote: Is it possible to run an insert,update, or delete and have it not launch a trigger like it normally would? alter table disable trigger ... http://www.postgresql.org/docs/8.2/static/sql-altertable.html ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Make a SQL statement not run trigger
On 8/21/07, Jon Collette <[EMAIL PROTECTED]> wrote: > Is it possible to run an insert,update, or delete and have it not launch > a trigger like it normally would? alter table disable trigger ... http://www.postgresql.org/docs/8.2/static/sql-altertable.html ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[SQL] Make a SQL statement not run trigger
Is it possible to run an insert,update, or delete and have it not launch a trigger like it normally would? For example could I set a value DONOTRUN = True; insert into contacts Where the trigger on contacts would call a function that would have an IF statment for that DONOTRUN value? Or is there just a global variable I could set to disable triggers and then reset it? And would that be a per connection variable? Thanks ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Join question
On Aug 21, 2007, at 12:48 , [EMAIL PROTECTED] wrote: SELECT a.x, max(b.x) FROM a, b, c WHERE a.a_id = c.a_id AND b.b_id = c.b_id GROUP by a.x; Shouldn't affect performance, but another way to write this which you may find more readable is to list your join conditions with the joins rather than grouping them all in the WHERE clause: SELECT a.x, max(b.x) FROM a JOIN c USING (a_id) JOIN b USING (b_id) GROUP BY a.x; You can also write this using NATURAL JOIN which joins on like-named columns. SELECT a.x, max(b.x) FROM a NATURAL JOIN c JOIN b USING (b_id) GROUP BY a.x; You can't use NATURAL JOIN to join b because you've got columns named x in both a and b that you're *not* joining on. Another nice thing about the USING and NATURAL JOIN syntax is that the result includes only one column for the joined columns, rather than two. In this case, there would be only one b_id and one a_id column in the result set. Using ON or putting the join condition in the WHERE clause puts two b_id and two a_id columns in the result set. Does anyone know a way I could restructure this query to get only one b for each a in a faster way? You might want to ask on the pgsql-performance list as well, as people there are generally interested in improving query performance. One thing they'll ask you for is the output of EXPLAIN ANALYZE for your query. Here's an alternative, but I don't know how it'd compare in terms of performance: EXPLAIN ANALYZE SELECT DISTINCT ON (a.x) a.x, b.x FROM a NATURAL JOIN c JOIN b USING (b_id); Give that a shot. (DISTINCT ON is a non-standard PostgreSQL extension, if non-portable syntax is something you're looking to avoid.) EXPLAIN ANALYZE shows you how the planner decided to proceed with the query, which can be useful when comparing alternatives, so you can compare using DISTINCT ON with your own query using max. Hope this helps. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] SELECT syntax synopsis: column_definition?
Can any one give an example of the difference between a column_alias and a column_definition when using a function in the FROM clause? from the manual: http://www.postgresql.org/docs/8.2/interactive/sql-select.html "function_name ( [ argument [, ...] ] ) [ AS ] alias [ ( column_alias [, ...] | column_definition [, ...] ) ]" Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Join question
oops... I meant "DISTINCT ON ( a_id )" --- Richard Broersma Jr <[EMAIL PROTECTED]> wrote: > SELECT a.x, b.x > FROM ( SELECT DISTINCT ON ( a_id ) a_id, b_id ^^ >FROM c ) AS c( a_id, b_id ) > INNER JOIN a > ON c.a_id = a.id > INNER JOIN b > ON c.b_id = b.id; > > Regards, > Richard Broersma Jr. > > > > ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Join question
--- [EMAIL PROTECTED] wrote: > create table c ( >a_id int, >b_id int > ); > > I am doing a query like this: > > SELECT a.x, max(b.x) FROM a, b, c WHERE a.a_id = c.a_id AND b.b_id = > c.b_id GROUP by a.x; > > I only need to get one row from b for each row in a, and it really > doesn't matter which one. I use max() to get a single value from table > b. There are generally be dozens to hundreds of rows in b for each row > in a. The problem is when I have a query with tens of thousands of rows > in a that the join with b will have millions of rows, and is really > slow. The group by effectively reduces the results down to what I want, > but it still has to process the millions of rows. Does anyone know a > way I could restructure this query to get only one b for each a in a > faster way? CREATE INDEX table_c_foreign_key ON c ( a, b ); SELECT a.x, b.x FROM ( SELECT DISTINCT( a_id ) a_id, b_id FROM c ) AS c( a_id, b_id ) INNER JOIN a ON c.a_id = a.id INNER JOIN b ON c.b_id = b.id; Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] Join question
Hey All, I have a query I'm trying to speed up, and I was hoping someone could help me. I have a three tables a and b hold data, and c just references between a and b: create table a ( a_id int, x int ); create table b ( b_id int, x int ); create table c ( a_id int, b_id int ); I am doing a query like this: SELECT a.x, max(b.x) FROM a, b, c WHERE a.a_id = c.a_id AND b.b_id = c.b_id GROUP by a.x; I only need to get one row from b for each row in a, and it really doesn't matter which one. I use max() to get a single value from table b. There are generally be dozens to hundreds of rows in b for each row in a. The problem is when I have a query with tens of thousands of rows in a that the join with b will have millions of rows, and is really slow. The group by effectively reduces the results down to what I want, but it still has to process the millions of rows. Does anyone know a way I could restructure this query to get only one b for each a in a faster way? Thanks, Ed Tyrrill ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] wrong answer
A. R. Van Hook wrote: I have a simple (and incorrect queuer) that gives the wrong answer. Can someone show me the correct syntax? t "select sum(s.ref), r.value, s.ref from registry as s ^^^ Simple typo - you've used sum() not count() qs "select count(*) from registry where ref =3" -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[SQL] wrong answer
I have a simple (and incorrect queuer) that gives the wrong answer. Can someone show me the correct syntax? t "select sum(s.ref), r.value, s.ref from registry as s left join referralkey as r on (s.ref = r.cd) group by s.ref, r.value order by r.value, s.ref" sum | value | ref --+-+- 3462 | A Friend| 3 150 | Bridal Guide| 6 33 | Coupon | 11 yet qs "select count(*) from registry where ref =3" count = 1154 qs "select count(*) from registry where ref=6" count = 25 qs "select count(*) from registry where ref=11" count = 3 thanks -- Arthur R. Van Hook [EMAIL PROTECTED] [EMAIL PROTECTED] (816) 578-4704 - Home (816) 629-0071 Cell ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
