[SQL] Proposed dbmirror change
hi again
i made one mod to dbmirror.
1) I implemented a mechanism to exclude attributes (columns) from
dbmirror'ed tables.
I added one table
CREATE TABLE dbmirror_exclude_attributes (
tblname character varying(100) NOT NULL,
attnames text[] NOT NULL
);
ALTER TABLE ONLY dbmirror_exclude_attributes
ADD CONSTRAINT dbmirror_exclude_attributes_pkey PRIMARY KEY (tblname);
and one function
bool isExcluded(char *cpTableName,TupleDesc tTupleDesc, int iColumnCounter);
which is called in packageData, and examines to see if this
column should be excluded.
The contents of dbmirror_exclude_attributes are like:
dynacom=# SELECT * from dbmirror_exclude_attributes;
tblname | attnames
--+---
"public"."mariner" | {parentid,relationtypeid}
"public"."marinerpapers" | {mpaid}
(2 rows)
dynacom=#
One use of it would be the selective mirroring of only a subset of
all of one table's columns, excluding e.g. huge bytea columns,
where the communication link is for instance ultra expensive and
unreliable SAT connection.
Another use case would be the exclusion of sensitive information
like credit card numbers or medical data.
What do you all think?
-Achilleus
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] Permission to Select
Markus Schaber wrote: Hi, Eugene, Eugene E. wrote: This means that some privileges are NOT INDEPENDENT. No, it means that the UPDATE operation needs both UPDATE and SELECT privileges. Markus thanx. I already clear this to me. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] cursor and for update
On 03/28/06 01:35, Maciej Piekielniak wrote: >Hello , > >I try to translate my old functions from oracle but don't understand what is >wrong. > >create or replace function uporzadkuj_reguly(text,text) RETURNS integer AS >' >DECLARE > tabela ALIAS FOR $1; > lancuch ALIAS FOR $2; > ret integer:=0; > licznik integer:=1; > rekord firewall%ROWTYPE; > > reguly CURSOR FOR SELECT * from firewall ORDER BY id_firewall WHERE > tabela=tabela and lancuch=lancuch for UPDATE; >BEGIN > for i in reguly LOOP > UPDATE firewall SET id_firewall=licznik WHERE CURRENT OF reguly; > licznik:=licznik+1; > END LOOP; > > return ret; >END;' >LANGUAGE 'plpgsql'; > > > Fist, what's the error you get? Second, what does "rekord" do? You don't seem to use it. Third, can you quote the language? I always say plpgsql without the quotes, but I'm not sure if using quotes is not allowed (and I'm too lazy to check :)). Also, why don't you have a "RETURNS VOID" function, which you can end with "RETURN;" But what you might be stuck on is that you have a column and variable with the same name. Try to give the variables "tabela" and "lancunch" different names. Lastly, if you use postgres 8, you can quote your function with $$ instead of ' (meaning ...$$ AS DECLARE BEGIN END;$$...), so that you won't get the ugly syntax highligting that most editors will give you when quoting with '. signature.asc Description: OpenPGP digital signature
[SQL] cursor and for update
Hello , I try to translate my old functions from oracle but don't understand what is wrong. create or replace function uporzadkuj_reguly(text,text) RETURNS integer AS ' DECLARE tabela ALIAS FOR $1; lancuch ALIAS FOR $2; ret integer:=0; licznik integer:=1; rekord firewall%ROWTYPE; reguly CURSOR FOR SELECT * from firewall ORDER BY id_firewall WHERE tabela=tabela and lancuch=lancuch for UPDATE; BEGIN for i in reguly LOOP UPDATE firewall SET id_firewall=licznik WHERE CURRENT OF reguly; licznik:=licznik+1; END LOOP; return ret; END;' LANGUAGE 'plpgsql'; -- Best regards, Maciej mailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] unique names in variables and columns in plsql functions
(Whoops, pressed wrong reply button. Here it is correctly this time.) On 03/27/06 17:02, Tom Lane wrote: >It'll retrieve whatever the current value of the plpgsql variable >provider_id is. plpgsql always assumes that ambiguous names refer >to its variables (indeed, it isn't even directly aware that there's >any possible ambiguity here). > > That's why I suspected it would be NULL, since provider_id wasn't initialised yet. > >How and why would it determine that? In general it's perfectly normal >to use plpgsql variable values in SQL commands. I don't think it'd make >the system more usable if the parser tried to apply a heuristic rule >about some occurrences being meant as variable references and other ones >not. If the rule ever got it wrong, it'd be even more confusing. > I guess I'm looking at it too much as a human. I said "SELECT FROM" so, it guess I assumed it would be clear enough which one it had to use. But, now that I'm thinking about it some more, I agree. However, a fatal error would also have been welcome. signature.asc Description: PGP signature signature.asc Description: OpenPGP digital signature
Re: [SQL] unique names in variables and columns in plsql functions
(Whoops, pressed wrong reply button. Here it is correctly this time.) On 03/27/06 16:48, Jim C. Nasby wrote: > >Sadly, overloading variable names between plpgsql and SQL is *highly* >problematic. Because of this I *always* prefix plpgsql variables with >something, such as p_ for parameters and v_ for general variables. > > Hmm. Well, at least now I'm aware of it. Perhaps I will do something similair to prefixes from now on, it would seem to be good practice. signature.asc Description: OpenPGP digital signature
Re: [SQL] Bitfields always atomic? Other way to store attributes?
Am Sonntag, 26. März 2006 23:47 schrieb Bryce Nesbitt:
> Dear List;
>
> If I have two threads modifying the same "bit" field:
> thread1=> update table set bf=bf | '01000'
> thread2=> update table set bf=bf | '1'
> Will this operation always be safe (e.g. result in bf='11000')?
yes, Thats what "ACID" (http://en.wikipedia.org/wiki/ACID) is all about.
> Or must
> I wrap things in
> explicit transactions?
every statement is in it's own transaction as long as you dont start one by
yourself.
> My application is to give attributes to an address table. But maybe
> there is a better way?
>
> I want to mark each addresses with attributes, e.g. the person may be a
> "friend", "on my holiday card list", "owe me money", be an "employee", a
> "volunteer on the xxx project", or none of the above.
>
> I could assign each role a bit.
>
> Or, create a string field: "Friend,Money, Emp,VolXXX".
>
> Or, create related tables:
> friend_pk, address_id
> cardlist_pk, address_id
> money_pk,address_id,amount_owed
> volunteer_pk,address_id
>
> Any thoughts?
create a table with attributes and a table with addresse "address" and then
link them via a third table address_addressattributes, something like this:
create table address (
add_id serial not null primary key,
add_name text not null,
add_street ...
...
);
create table addressattributes (
aa_id serial not null primary key,
aa_name text not null unique
);
insert into address_attributes (aa_name) values ('Friend');
insert into address_attributes (aa_name) values ('Money');
create table address_addressattributes (
add_aa_id serial primary key,
aa_id int4 not null references address_attributes (aa_id),
add_id int4 not null references address (add_id)
)
this is called a many-to-many relation.
kind regards,
janning
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] Bitfields always atomic? Other way to store attributes?
Bryce Nesbitt wrote: > If I have two threads modifying the same "bit" field: > thread1=> update table set bf=bf | '01000' > thread2=> update table set bf=bf | '1' > Will this operation always be safe (e.g. result in bf='11000')? Or > must I wrap things in > explicit transactions? Each of these commands will be its own transaction if you don't explicitly start one. > My application is to give attributes to an address table. But maybe > there is a better way? Create 5 boolean fields. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] psqlODBC driver -- too many tables shown
I'm using the current Windows psqlODBC driver. "Show system" tables is turned off, but not working. When I use an ODBC client I see all tables... including system tables, and views & tables for which no permission exists. Anyone else see this? And where can I submit a bug report (I've looked, but just become confused as to the proper location). Thanks. -Bryce -- Visit http://www.obviously.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] Bitfields always atomic? Other way to store attributes?
Dear List; If I have two threads modifying the same "bit" field: thread1=> update table set bf=bf | '01000' thread2=> update table set bf=bf | '1' Will this operation always be safe (e.g. result in bf='11000')? Or must I wrap things in explicit transactions? My application is to give attributes to an address table. But maybe there is a better way? I want to mark each addresses with attributes, e.g. the person may be a "friend", "on my holiday card list", "owe me money", be an "employee", a "volunteer on the xxx project", or none of the above. I could assign each role a bit. Or, create a string field: "Friend,Money, Emp,VolXXX". Or, create related tables: friend_pk, address_id cardlist_pk, address_id money_pk,address_id,amount_owed volunteer_pk,address_id Any thoughts? -Bryce Nesbitt -- Visit http://www.obviously.com/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Problem using set-returning functions
Markus Schaber <[EMAIL PROTECTED]> writes: > navteq=# select foo,generate_x(bar) from test; > ERROR: set-valued function called in context that cannot accept a set > CONTEXT: PL/pgSQL function "generate_x" line 5 at return next plpgsql SRFs don't support being called from the SELECT target list, only from a FROM-expression. Your other function is probably written in SQL not plpgsql; SQL functions do support this. plpgsql could probably be fixed if we were really motivated to do so, but I think most people who have looked at it feel we should phase out the capability to call SRFs from a target list, rather than extend it. It's weird and not very semantically sound --- in particular, there's no very sensible definition if there's more than one of them in the target list. See past discussions in the PG archives. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Problem using set-returning functions
Hi, Stephan, Stephan Szabo wrote: > It's an implementation detail. Some languages handle SRFs in a way that > can be handled in the select list (SQL and C I think) and others do not > (plpgsql). Ah, that's an enlightening explanation, thanks. > The latter will likely change at some point, although there are > some confusing issues with SRFs in the select list as well, see the > difference in behavior between: > > select generate_series(1,10), generate_series(1,5); > vs > select * from generate_series(1,10) g1, generate_series(1,5) g2; I know that the SRF special semantics are ugly, and would vote for adjacent tables to be implemented as replacement. Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Expressing a result set as an array (and vice versa)?
SELECT array_accum( DISTINCT list_id ) FROM bookmarks;
array_accum
---
{1,2,3,4,5,7}
Couldn't you just use array()?
Yes, you can do this :
SELECT ARRAY( SELECT something with one column );
However, array_accum() as an aggregate is more interesting because you
can use GROUP BY. For instance :
SELECT parent, array_accum( child ) FROM table GROUP BY parent;
I have another question. Suppose I have these tables :
CREATE TABLE items (
id SERIAL PRIMARY KEY,
categoryINTEGER NOT NULL,
nameTEXT NOT NULL,
);
CREATE TABLE comments (
item_id INTEGER NOT NULL REFERENCES items(id),
id SERIAL PRIMARY KEY,
comment TEXT NOT NULL,
added TIMESTAMP NOT NULL DEFAULT now()
)
Say I want to display some items and the associated comments :
SELECT * FROM items WHERE category = ...
Then, I gather the item ids which were returned by this query, and do :
SELECT * FROM comments WHERE item_id IN ( the ids ) ORDER BY item_id,
added;
Is there a more elegant and efficient way which would avoid making a big
IN() query ? I could join comments with items, but in my case the search
condition on items is quite complicated and slow ; hence I only want to do
the search once. And I have several different tables in the same style of
the "comments" table, and so I make several queries using the same IN
(...) term. It isn't very elegant... is there a better way ? Use a
temporary table ? How do you do it ?
---(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] Problem using set-returning functions
On Mon, 27 Mar 2006, Markus Schaber wrote: > Hi, John, > > John DeSoi wrote: > > > With SRFs, you need to specify what you want to select. In other words > > if you are calling generate_x(bar) you need "select * from > > generate_x(bar)" -- "select generate_x(bar)" will not work. > > So, then, why does it work with generate_series() and dump()? It's an implementation detail. Some languages handle SRFs in a way that can be handled in the select list (SQL and C I think) and others do not (plpgsql). The latter will likely change at some point, although there are some confusing issues with SRFs in the select list as well, see the difference in behavior between: select generate_series(1,10), generate_series(1,5); vs select * from generate_series(1,10) g1, generate_series(1,5) g2; ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] unique names in variables and columns in plsql functions
On Mon, Mar 27, 2006 at 10:02:24AM -0500, Tom Lane wrote: > Wiebe Cazemier <[EMAIL PROTECTED]> writes: > > DECLARE > > provider_id INTEGER; > > BEGIN > > provider_id := (SELECT provider_id FROM investment_products WHERE id = > > my_new.investment_product_id); > > END; > > > After a lot of trouble, I found out this line doesn't work correctly > > with the variable name as it is. It doesn't give an error or anything, > > it just retrieves some wrong value (probably NULL). > > It'll retrieve whatever the current value of the plpgsql variable > provider_id is. plpgsql always assumes that ambiguous names refer > to its variables (indeed, it isn't even directly aware that there's > any possible ambiguity here). > > > I was somewhat surprised to discover this. Can't Postgres determine that > > the provider_id in the SELECT statement is not the same one as the variable? > > How and why would it determine that? In general it's perfectly normal > to use plpgsql variable values in SQL commands. I don't think it'd make > the system more usable if the parser tried to apply a heuristic rule > about some occurrences being meant as variable references and other ones > not. If the rule ever got it wrong, it'd be even more confusing. BTW, I believe SELECT investment_products.provider_id would work here, but I'm too lazy to test that theory out. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] unique names in variables and columns in plsql functions
Wiebe Cazemier <[EMAIL PROTECTED]> writes: > DECLARE > provider_id INTEGER; > BEGIN > provider_id := (SELECT provider_id FROM investment_products WHERE id = > my_new.investment_product_id); > END; > After a lot of trouble, I found out this line doesn't work correctly > with the variable name as it is. It doesn't give an error or anything, > it just retrieves some wrong value (probably NULL). It'll retrieve whatever the current value of the plpgsql variable provider_id is. plpgsql always assumes that ambiguous names refer to its variables (indeed, it isn't even directly aware that there's any possible ambiguity here). > I was somewhat surprised to discover this. Can't Postgres determine that > the provider_id in the SELECT statement is not the same one as the variable? How and why would it determine that? In general it's perfectly normal to use plpgsql variable values in SQL commands. I don't think it'd make the system more usable if the parser tried to apply a heuristic rule about some occurrences being meant as variable references and other ones not. If the rule ever got it wrong, it'd be even more confusing. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] pgsql2shp - Could not create dbf file
Hi, Fay, First, this would be better posted to the PostGIS lists, as pgsql2shp is a PostGIS utility, and not a PostgreSQL SQL query. Fay Du wrote: > The command I used is: pgsql2shp -f newroads gisdb testarea > > Where, newroads is my out put file name, gisdb is database name, > and testarea is table name. > > After hit enter button, I got the message: Initializing... Could > not create dbf file It seems that there was an error creating the newroads.dbf file - each shapefile consists of corresponding .shp, .dbf and .shx files. Do you have permissions on the directory to create those three files? Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] unique names in variables and columns in plsql functions
On Mon, Mar 27, 2006 at 04:33:55PM +0200, Wiebe Cazemier wrote: > Hi, > > In a plpgsl function, consider the following excerpt: > > DECLARE > provider_id INTEGER; > BEGIN > provider_id := (SELECT provider_id FROM investment_products WHERE id = > my_new.investment_product_id); > END; > > After a lot of trouble, I found out this line doesn't work correctly > with the variable name as it is. It doesn't give an error or anything, > it just retrieves some wrong value (probably NULL). When I change the > variable name to anything other than "provider_id", it works OK. > > I was somewhat surprised to discover this. Can't Postgres determine that > the provider_id in the SELECT statement is not the same one as the variable? Sadly, overloading variable names between plpgsql and SQL is *highly* problematic. Because of this I *always* prefix plpgsql variables with something, such as p_ for parameters and v_ for general variables. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] unique names in variables and columns in plsql functions
Hi, In a plpgsl function, consider the following excerpt: DECLARE provider_id INTEGER; BEGIN provider_id := (SELECT provider_id FROM investment_products WHERE id = my_new.investment_product_id); END; After a lot of trouble, I found out this line doesn't work correctly with the variable name as it is. It doesn't give an error or anything, it just retrieves some wrong value (probably NULL). When I change the variable name to anything other than "provider_id", it works OK. I was somewhat surprised to discover this. Can't Postgres determine that the provider_id in the SELECT statement is not the same one as the variable? signature.asc Description: OpenPGP digital signature
Re: [SQL] Problem using set-returning functions
Hi Markus, On Mar 27, 2006, at 9:00 AM, Markus Schaber wrote: So, then, why does it work with generate_series() and dump()? I'm not sure. All I know is I spent a while the other day puzzling over the same error message you had and finally realized I had to add a select expression to fix it. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] [postgis-users] Problem using set-returning functions
Hi, Regina, Obe, Regina DND\MIS wrote: > Actually I suspect no set returning function created in pgsql will work > the way you are trying to do it. Not sure why. The dump is done in c > and plugged in and I think it explicitly generates a tuple in the target > set for every output. > > Try rewriting your function to something like this and see if it works > > CREATE OR REPLACE FUNCTION generate_x (geom geometry) > RETURNS SETOF double precision AS > ' > SELECT X(geometryn($1,index)) > FROM generate_series(1, npoints($1)) index; > > ' LANGUAGE 'sql' IMMUTABLE STRICT; Yes, it seems to work. This will solve the OPs case. But it is still a good question whether it is possible to accomplish this using plpgsql. Thanks, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Permission to Select
Hi, Eugene, Eugene E. wrote: > This means that some privileges are NOT INDEPENDENT. No, it means that the UPDATE operation needs both UPDATE and SELECT privileges. Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(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] pgsql aggregate: conditional max
Hi, Weimao Ke, Weimao Ke wrote: >> SELECT aid, cat >> FROM tablename AS t >> JOIN (SELECT aid, max(weight) AS weight >> FROM tablename >> GROUP BY aid) AS s USING (aid, weight); >> > This query will return duplicates if there are multiple categories (for > one aid) with the same max weight. Yet, I should be able to remove the > duplicates somehow...:) Try SELECT DISTINCT aid, cat FROM tablename AS t JOIN (SELECT aid, max(weight) AS weight FROM tablename GROUP BY aid) AS s USING (aid, weight); HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(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] Problem using set-returning functions
Hi, John, John DeSoi wrote: > With SRFs, you need to specify what you want to select. In other words > if you are calling generate_x(bar) you need "select * from > generate_x(bar)" -- "select generate_x(bar)" will not work. So, then, why does it work with generate_series() and dump()? Confused, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Find min and max values across two columns?
On Fri, Mar 24, 2006 at 04:00:35PM -0500, Tom Lane wrote: > Amos Hayes <[EMAIL PROTECTED]> writes: > > I'm trying to build a query that among other things, returns the > > minimum and maximum values contained in either of two columns. > > I think you might be looking for > > select greatest(max(columnA), max(columnB)) from tab; > select least(min(columnA), min(columnB)) from tab; > > greatest/least are relatively new but you can roll your own in > older PG releases. And if you care about performance you might also try: SELECT max(greatest(column_a, column_b) ... SELECT min(least(column_a, column_b) ... There may be a difference in performance between the two. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Question about One to Many relationships
On Fri, Mar 24, 2006 at 06:29:25PM +0100, PFC wrote: > > > >>And I want to link the band to the album, but, if the album is a > >>compilation it'll be linked to multiple band.ids, so i can't just add > >>a column like: > > For a compilation, you should link a band to a track, not an album. > This opens another can of worms... > > I would use the following tables : BTW, if you're going to be writing code to manage stuff like this, you should absolutely check out the source for http://musicbrainz.org/, which uses PostgreSQL as it's backend. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] SQL Query Newbie Help
On Fri, Mar 24, 2006 at 02:29:10PM -0800, Stephan Szabo wrote: > > On Fri, 24 Mar 2006, Julie Robinson wrote: > > > This works, but is there a better solution? > > > > select * > > from quality_control_reset T > > where date = ( > > select max(date) > > from quality_control_reset > > where qualitycontrolrange = T.qualitycontrolrange); > > If you can use PostgreSQL extensions (and don't care that you might not > get two rows if two ids had the same date equaling the max date for a > given range), maybe something like: > > select distinct on (qualitycontrolrange) id, date, qualitycontrolrange > from quality_control_reset order by qualitycontrolrange,date desc; > > > Otherwise, you might see how the above compares in plan to something like > (not really tested): > > select T.* from quality_control_reset T inner join > (select qualitycontrolrange, max(date) as date from quality_control_reset > group by qualitycontrolrange) T2 > on (T.qualitycontrolrange = T2.qualitycontrolrange and T.date=T2.date); BTW, I believe the new row operator fixes in 8.2 make it possible to use them to do this kind of thing as well... -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Expressing a result set as an array (and vice versa)?
On Sat, Mar 25, 2006 at 12:17:08AM +0100, PFC wrote:
>
>
> CREATE OR REPLACE FUNCTION foreach( liste INTEGER[] ) RETURNS SETOF
> INTEGER AS $$
> DECLARE
> i INTEGER;
> BEGIN
> FOR i IN 1..icount(liste) LOOP
> RETURN NEXT liste[i];
> END LOOP;
> END;
> $$ LANGUAGE plpgsql;
Seems like this should really exist in the backend...
> CREATE AGGREGATE array_accum (
> sfunc = array_append,
> basetype = anyelement,
> stype = anyarray,
> initcond = '{}'
> );
>
> SELECT array_accum( DISTINCT list_id ) FROM bookmarks;
> array_accum
> ---
> {1,2,3,4,5,7}
Couldn't you just use array()?
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [SQL] Problem using set-returning functions
On Mar 27, 2006, at 5:41 AM, Markus Schaber wrote: navteq=# select foo,generate_x(bar) from test; ERROR: set-valued function called in context that cannot accept a set CONTEXT: PL/pgSQL function "generate_x" line 5 at return next However, it is fine to call other set returning functions in the same context: With SRFs, you need to specify what you want to select. In other words if you are calling generate_x(bar) you need "select * from generate_x(bar)" -- "select generate_x(bar)" will not work. So for your query I think you need something like: select foo, (select x from generate_x(bar)) from test; John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] functions in WHERE clause
Hi, Steve, [EMAIL PROTECTED] wrote: > SELECT * from some_table WHERE > test_for_equality_is_syntactically_ugly; > The WHERE clause expects the function to return a boolean value. I > can certainly return a boolean value from a function, but here it > seems to me that what the function really has to do is return a > set of boolean values -- the test in the WHERE clause sometimes > evaluates to true and sometimes evaluates to false, and that is in > turn used to constrain the query results. But you can't return a > set of anything (I don't think) in a WHERE clause, because it seems > to want a singular boolean value. And this is as it is intended. The equality test is applied row-by-row, and for each row, it is either true or false, but not undecided. So your query should look like SELECT * FROM some_table WHERE your_function(column_a, column_b); > Is it possible to do what I'm trying to do? I've written a few > simple sql and pl/pgsql functions over the years, but I'm no expert. Yes, it is. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] [postgis-users] Problem using set-returning functions
Hi, Regina, Obe, Regina DND\MIS wrote: > generate_series works I think because it is a special case - it isn't > really a set function even though it behaves that way sortof. Well, dump() works as well, and it is no build-in. As a recent thread here stated, it is how set returning functions are expected to work as long as "adjacent tables" are not implemented. http://www.mail-archive.com/pgsql-sql@postgresql.org/msg20545.html Thanks, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] Problem using set-returning functions
Hi,
I'm cross-posting this to the plpgsql list:
We've run into a small problem creating a set-returning function for
PostGIS in PostgreSQL 8.1.0:
CREATE OR REPLACE FUNCTION generate_x (geom geometry)
RETURNS SETOF double precision AS
'DECLARE
index integer;
BEGIN
FOR index IN 1 .. npoints(geom) LOOP
RETURN NEXT X(geometryn(geom,index));
END LOOP;
END
' LANGUAGE 'plpgsql' IMMUTABLE STRICT;
Now, trying to use this function yields the following error:
navteq=# select foo,generate_x(bar) from test;
ERROR: set-valued function called in context that cannot accept a set
CONTEXT: PL/pgSQL function "generate_x" line 5 at return next
However, it is fine to call other set returning functions in the same
context:
navteq=# select foo,dump(bar) from test;
foo | dump
-+--
42 | ({1},010100F03F0040)
42 | ({2},01010008401040)
42 | ({3},01010014401840)
23 | ({1},0101001C402040)
(4 rows)
navteq=# select foo,x(geometryn(bar,generate_series(1,npoints(bar
FROM test;
foo | x
-+---
42 | 1
42 | 3
42 | 5
23 | 7
(4 rows)
(This third query is equal to what I expected the failing query to do.)
The table "test" looks as follows;
navteq=# \d test
Table "public.test"
Column | Type | Modifiers
+--+---
foo| integer |
bar| geometry |
navteq=# select foo,asText(bar) from test;
foo | astext
-+-
42 | MULTIPOINT(1 2,3 4,5 6)
23 | MULTIPOINT(7 8)
(2 rows)
I'm shure its a small detail I've blindly ignored, but I'm stuck ATM.
Thanks,
Markus
--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org
---(end of broadcast)---
TIP 6: explain analyze is your friend
