Re: [SQL] obtuse plpgsql function needs
You'll need to pass the values down to your
concat function (which I suggest you don't call concat)
and have it return a text type.
What exactly is your problem? I must be missing something.
elein
On Tue, Jul 22, 2003 at 06:31:52PM -0400, Robert Treat wrote:
> given
>
> create table t1 (f,f1,f2,f3);
> create table t2 (f,f4,f5,f6);
>
> i'm trying to create a function concat() that does something like:
>
> select f,concat() as info from t1;
>
> which returns a result set equivalent to:
> select f,('f1:' || f1 || '- f2:' || f2 || '- f3:' || f3) as x from t1;
>
> or
> select f,concat() as info from t2;
> returns equivalent
>
> select f,('f4:' || f4 || ' - f5:' || f5 || ' - f6:' || f6) as x from t2;
>
>
> I'm starting to believe this is not possible, has anyone already done
> it? :-)
>
> Robert Treat
> --
> Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
>
>
> ---(end of broadcast)---
> TIP 8: explain analyze is your friend
>
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] obtuse plpgsql function needs
How will you know in your function what the field
names are (you won't) and how many fields to concat
unless the function would only work on a fixed number
of fields?
If it only works on a fixed number of fields,
you still have:
myconcat( text, text, text, text )
called by
select f, myconcat( f,f1,f2,f3) from t1;
and
select f, myconcat( f,f4,f5,f6) from t2;
The cost is typing in the param list.
For a variable length record it is trickier.
You can do it in C, of course.
The key pieces needed to do this are:
* Ability to pass a generic RECORD to a function.
This *might* be in 7.4 but I'm not sure.
myconcat( t1 ); or possibly myconcat (t1.*);
* Ability to know the number of columns in the RECORD
A pg_catalog query
* Ability to access the columns by order in a loop
AFAIK you have to access the columns by name.
If you can work through those issues, then
you'll have it. The pieces are available in
several areas, the generic types and languages
like plpython and plperl which may be able to
loop through a generic tuple, if they could input
a tuple.
I will hang onto this problem and if either of
us finds a solution, I'd like to publish it in
general bits.
elein
On Wed, Jul 23, 2003 at 09:06:49AM -0400, Robert Treat wrote:
> On Tue, 2003-07-22 at 19:33, elein wrote:
> > You'll need to pass the values down to your
> > concat function (which I suggest you don't call concat)
> > and have it return a text type.
> >
> > What exactly is your problem? I must be missing something.
> >
>
> The problem is that I need the function to be generic so that I don't
> have to pass the values down to the function, it just grabs the values
> automagically based on the table it's being called against.
>
> Robert Treat
>
> > elein
> >
> > On Tue, Jul 22, 2003 at 06:31:52PM -0400, Robert Treat wrote:
> > > given
> > >
> > > create table t1 (f,f1,f2,f3);
> > > create table t2 (f,f4,f5,f6);
> > >
> > > i'm trying to create a function concat() that does something like:
> > >
> > > select f,concat() as info from t1;
> > >
> > > which returns a result set equivalent to:
> > > select f,('f1:' || f1 || '- f2:' || f2 || '- f3:' || f3) as x from t1;
> > >
> > > or
> > > select f,concat() as info from t2;
> > > returns equivalent
> > >
> > > select f,('f4:' || f4 || ' - f5:' || f5 || ' - f6:' || f6) as x from t2;
> > >
> --
> Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
>
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] obtuse plpgsql function needs
So, other than C, plperl or pltcl is the way to go. As long as they can input generic composite types (I wasn't sure of that, but I should have known), they can access columns as array elements so you can loop through them. And they'll tell you the number of arguments. Ta da! elein On Wed, Jul 23, 2003 at 03:15:50PM -0400, Tom Lane wrote: > elein <[EMAIL PROTECTED]> writes: > > You can do it in C, of course. > > Yeah. Also you could do it easily in plperl or pltcl (composite-type > arguments get passed as perl hashes or Tcl arrays respectively). > plpgsql does not have any facility for run-time determination of field > names, so you're pretty much out of luck in that particular language. > > regards, tom lane > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] obtuse plpgsql function needs
Bruce-- Something for the todo list. This would be extremely handy. At minimum C functions should be able to ask the type of thing that was actually passed in and get a legitimate answer even if the type were a rowtype. This will also lead to the need for unnamed rowtypes, sooner or later. I know, I know, send a patch. --elein On Thu, Jul 24, 2003 at 01:07:18AM -0400, Tom Lane wrote: > elein <[EMAIL PROTECTED]> writes: > > So, other than C, plperl or pltcl is the way to go. > > As long as they can input generic composite types > > (I wasn't sure of that, but I should have known), > > Come to think of it, that is a problem: we don't have any way to declare > a function as taking "any tuple type". So even though pltcl or plperl > functions could be written to work with such input, we can't declare them. > This is a problem even for C functions. You could declare a C function > as taking "any", but then you can't even check that what you got was a > tuple ... > > Something to work on for 7.5, I suppose. > > regards, tom lane > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > >http://archives.postgresql.org > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Large Objects and Bytea
In this week's General Bits, we talk about using large objects. Perhaps this might help you understand what is involved with using them. http://www.varlena.com/GeneralBits/ cheers, [EMAIL PROTECTED] On Thu, Sep 25, 2003 at 09:41:28AM +0530, Kumar wrote: > Hi Friends, > > I am running Postgres 7.3.4 on RH Linux 7.2. > > I am migrating MS SQL DB to Postgres DB. I have tables with columns of data > type 'Image' in the MS SQL database. > IF I choose 'bytea' datatype, I am afraid it may lead to poor performance of > the database (which I read from the manual). In this case what is the best data > type to use. > > Please suggest me. The explanation of using LOB objects in a table and to write > them with image is not very clear in the documentation. Can any one send me any > link or white paper or examples about it. > > Thanks in advance. > > Kumar > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Running tally
You can use plpythonu (or tcl or C or R) to do running sums. For plpythonu, you must initialize the SD[] by calling it first with the proper argument. create or replace function runsum(int,int) returns int as ' if args[0] == 1: SD["currval"] = 0 return SD["currval"] else: try: SD["currval"] += args[1] except: SD["currval"] = args[1] return SD["currval"] ' language 'plpython'; select runsum(1,0); select num, runsum(0,num) from tallytable; Variations on this technique are discussed on General Bits http://www.varlena.com/GeneralBits under the Tidbits area listing talks from OSCON2003. [EMAIL PROTECTED] webstat=# select runsum(0,code), code, doc from temp_rawlogs; n Sat, Oct 04, 2003 at 05:56:38PM +0800, Christopher Kings-Lynne wrote: > Hi guys, > > If I have a table that is just a single column full of numbers, how can > I select all the rows from the table with a second column that is the > running tally so far down the result set? > > eg: > > Num Tally so far > 0.3 0.3 > 1.2 1.5 > 2.0 3.5 > ... > > Does this require PL/PgSQL coding? If so, how do you actually construct > an arbitrary row for returning? The docs are somewhat unclear on this. > > Chris > > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] CHAR(n) always trims trailing spaces in 7.4
This is an example of the problem. It used to expand the middle thing to 15. elein=# select 'x' || ' '::char(15) || 'x'; ?column? -- xx (1 row) On Tue, Feb 17, 2004 at 06:10:56PM -0500, Tom Lane wrote: > "news.postgresql.org" <[EMAIL PROTECTED]> writes: > > I just discovered the following change to CHAR(n) (taken from varlena.com, > > general bits, issue 62). > > The description you quote doesn't appear to have much of anything to do > with the actual behavior of 7.4. > > 7.4 will trim trailing spaces when converting char(n) to varchar or > text, but the example query does not do that. It just coerces query > output columns to char(n), and that works the same as it did before. > For instance > > regression=# select 'zit'::char(77); > bpchar > --- > zit > (1 row) > > > regards, tom lane > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] CHAR(n) always trims trailing spaces in 7.4
So the problem is there. But blaming it on char was wrong. It should be blamed on the varchar change. Hey, I thought the truncation was for varchar and not text? It was for both? It would be semantically tricky to change the operator. The precendence is to convert to text. Now with the implicit update of the char(n) to text for the operator "corrupts" the char() value. elein On Tue, Feb 17, 2004 at 06:40:49PM -0500, Tom Lane wrote: > elein <[EMAIL PROTECTED]> writes: > > This is an example of the problem. It used to expand > > the middle thing to 15. > > > elein=# select 'x' || ' '::char(15) || 'x'; > > ?column? > > -- > > xx > > (1 row) > > Still does, but then the spaces go away again when the value goes into > the concatenation, because concatenation is a text operator. > > regards, tom lane > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > >http://archives.postgresql.org ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] CHAR(n) always trims trailing spaces in 7.4
So exactly what is the order of casts that produces different results with: 'x' || ' ' || 'x' and 'x' || ' '::char15 || 'x' Are operators being invoked both (text,text)? I'm trying to understand the precedence that causes the different results. elein On Tue, Feb 17, 2004 at 10:53:17PM -0500, Tom Lane wrote: > elein <[EMAIL PROTECTED]> writes: > > Apparently the ::char is cast to varchar and then text? > > No, directly to text, because the || operator is defined as taking text > inputs. But there's no practical difference between text and varchar on > this point. > > regards, tom lane > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > >http://archives.postgresql.org ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] CHAR(n) always trims trailing spaces in 7.4
I guess I am asking about the cast sequence from
char(n) to text.
(' '::char(n))::text trims spaces. This is wrong, imo.
' '::text does not trim spaces.
' '::char(n) does not trim spaces and pads.
char(n) should not trim spaces, right? And
it doesn't on an insert. Text does not trim spaces.
Somewhere the space trimming occurs.
If it is in the operator || then the operator is wrong.
If char(n) is properly defined to not trim spaces then
there should be a separate cat for char(n). It is correct
for it to behave differently than cat for text and varchar
because of the different trimming behaviour.
I can do this patch if there is agreement. But
I may not be able to do it immediately.
elein
On Wed, Feb 18, 2004 at 11:58:37PM -0500, Tom Lane wrote:
> elein <[EMAIL PROTECTED]> writes:
> > So exactly what is the order of casts that produces
> > different results with:
>
> > 'x' || ' ' || 'x' and 'x' || ' '::char15 || 'x'
>
> > Are operators being invoked both (text,text)?
>
> The only relevant operator is "text || text" (there are also some ||
> operators for arrays, bytea, and BIT, but these will all be discarded
> as not the most plausible match). Therefore, in your first example the
> unspecified literals will all be presumed to be text, so the space does
> not get trimmed.
>
> One of the things we could think about as a way to tweak the behavior is
> creating "||" variants that are declared to accept char(n) on one or
> both sides. These could actually use the same C implementation function
> (textcat) of course. But declaring them that way would suppress the
> invocation of rtrim() as char-to-text conversion.
>
> However, if we did that then "||" would behave differently from other
> operators on character strings, so it doesn't seem like a very
> attractive option to me.
>
> regards, tom lane
>
> ---(end of broadcast)---
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Function To Log Changes
A plpython solution is available in Issue #66 of PostgreSQL GeneralBits. http://www.varlena.com/GeneralBits/66 Let me know if this helps. elein On Mon, Apr 05, 2004 at 01:01:39PM -0400, Gavin wrote: > Hi All, I have been tinkering with a function to log the changes made on > any column through a function and trigger. However, I cant think of a way > to make this work through pl/pgsql. Any one have any ideas, or is it just > not possible? > > SNIP > create or replace function logchange2() returns OPAQUE as ' > DECLARE > columnname record; > c2 VARCHAR(64); > > BEGIN > > /* Cycle through the column names so we can find the changes being made */ > FOR columnname IN SELECT attname FROM pg_attribute, pg_type > WHERE attnum > 0 AND typrelid=attrelid AND typname=''SOMETABLE'' LOOP > > c2 := CAST(columnname.attname AS VARCHAR(64)); > /* here lies the problem. How would I make plpgsql see OLD.columnname in > a dynamic fashion. I know this wont work whats below, but I am just > trying to express what I am trying to do */ > IF ''OLD.'' || c2 != ''NEW.'' || c2 THEN > /* IF CHANGED DO SOMETHING */ > RAISE NOTICE ''Update on column %'', c2; > END IF; > > END LOOP; > > return NULL; > END; > ' > LANGUAGE plpgsql; > > create trigger logchange2 AFTER UPDATE on TABLENAME FOR EACH ROW EXECUTE > PROCEDURE logchange2(); > > > ---(end of broadcast)--- > TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] SQL challenge--top 10 for each key value?
Welcome to the real world, Josh. There are people who have full time salaried positions soley to attend standards meetings. Note that ROW_NUMBER() really is handy, regardless of the silly name. And there was a little python function of mine that did it fairly simply, except that you needed to initialize the counter in the connection before use. create or replace function pycounter(integer) returns integer as ' if args[0] == 0: SD["nextno"] = 1 return SD["nextno"] try: SD["nextno"] += 1 except: SD["nextno"] = 1 return SD["nextno"] ' language 'plpythonu'; And clearly it can be done faster as a little C function. elein On Fri, Apr 09, 2004 at 09:06:39AM -0700, Josh Berkus wrote: > Rod, > > > Something along the lines of the below would accomplish what you want > > according to spec. ROW_NUMBER() is a spec defined function. (6.10 of > > SQL200N) > > Great leaping little gods! They added something called "row number" to the > spec? > > Boy howdy, folks were right ... the ANSI committee really has completly blown > off the relational model completely. First there was the addition of > network-database functions so that IBM could make DB2 look more like a real > database, now this > > When a standards committee becomes hostage to a handful of vendors, kiss real > standards goodbye. > > -- > Josh Berkus > Aglio Database Solutions > San Francisco > > ---(end of broadcast)--- > TIP 8: explain analyze is your friend ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] SQL challenge--top 10 for each key value?
This solution will be in Monday's edition of
PostgreSQL General Bits (http://www.varlena.com/GeneralBits).
(In other words, if it doesn't do what you mean, let me know now!)
CREATE TYPE topscores AS
(id integer, query integer, checksum char(32), score integer);
CREATE OR REPLACE FUNCTION topscores(integer) RETURNS SETOF topscores AS
'
DECLARE
t topscores%ROWTYPE;
r RECORD;
q RECORD;
n alias for $1;
BEGIN
FOR q IN SELECT distinct query from table70 order by query LOOP
FOR t IN SELECT id , query, checksum, score
FROM table70
where query = q.query
ORDER BY query, score DESC LIMIT n LOOP
RETURN NEXT t;
END LOOP;
END LOOP;
RETURN;
END;
' language 'plpgsql';
select * from topscores(1) ;
select * from topscores(2) ;
select * from topscores(3) ;
On Thu, Apr 08, 2004 at 07:55:33PM +, Jeff Boes wrote:
> Offered up for anyone with time on their hands. I fiddled around with
> this for half an afternoon, then gave up and did it programmatically in
> Perl.
>
> Given a table that looks something like this:
>
> id | INTEGER
> query| INTEGER
> checksum | char(32)
> score| INTEGER
> include | BOOLEAN
>
>
> The table is unique by "id". "Checksum" may be repeated, but I only care
> if it is repeated within a given group by "query". ("query" is non-null.)
>
> I can get the top scorer for each "query" row by something like this:
>
> SELECT * FROM (
> SELECT DISTINCT ON (checksum) *
> FROM my_table
> ORDER BY checksum, score DESC)
> ORDER BY query;
>
> How would you go about getting the top N (say, the top 10) for each query?
>
> And then, if that's too easy for you--consider a further case where I
> want every row for a given "query" that has "include" TRUE, and enough
> non-"include" rows to make N. I might end up with more than N rows for a
> given value of "query" if there were more than N with "include" set.
>
> I headed off in the direction of groups of SELECTs and UNIONs, and quit
> when I got to something like four levels of "SELECT ... AS FOO" ...
>
> --
> Jeff Boes vox 269.226.9550 ext 24
> Database Engineer fax 269.349.9076
> Nexcerpt, Inc. http://www.nexcerpt.com
>...Nexcerpt... Extend your Expertise
>
>
> ---(end of broadcast)---
> TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] SQL challenge--top 10 for each key value?
No, it will not work twice in the same query as is. If you want to code two counter buckets and pass in some way to distinguish between the two yada yada yada it is possible. It is also possible to code this to do multi-level counting/breaks/calculations, etc. But the SD dictionary is by connection. So any values stored in it need to be initialized at the appropriate time *outside* of the first use. elein On Sun, Apr 11, 2004 at 12:38:20AM -0400, Greg Stark wrote: > > elein <[EMAIL PROTECTED]> writes: > > > create or replace function pycounter(integer) > > returns integer as > > ' > >if args[0] == 0: > > SD["nextno"] = 1 > > return SD["nextno"] > >try: > > SD["nextno"] += 1 > >except: > > SD["nextno"] = 1 > >return SD["nextno"] > > ' language 'plpythonu'; > > > > And clearly it can be done faster as a little > > C function. > > Does this approach have a hope of working if it's used twice in the same > query? > > > -- > greg > > > ---(end of broadcast)--- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] CHAR(n) always trims trailing spaces in 7.4
Apparently the ::char is cast to varchar and then text? That explains x || ' ' || x On Tue, Feb 17, 2004 at 05:07:24PM -0700, scott.marlowe wrote: > On Tue, 17 Feb 2004, Tom Lane wrote: > > > elein <[EMAIL PROTECTED]> writes: > > > This is an example of the problem. It used to expand > > > the middle thing to 15. > > > > > elein=# select 'x' || ' '::char(15) || 'x'; > > > ?column? > > > -- > > > xx > > > (1 row) > > > > Still does, but then the spaces go away again when the value goes into > > the concatenation, because concatenation is a text operator. > > But then this: > > select 'x'||' '||'x' > > should produce xx, but it produces x x. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] searching polygons
You should use some variation of overlaps or contains within. There is some discussion and a list of operators in Issue #61 of General Bits. ( http://www.varlena.com/GeneralBits/61 ) I would also suggest looking at the geometric operators in the documentation. You may have to cast the polygon to a circle to use the operators, but it will still tell you whether the smaller polys are contained within or overlap the larger. elein On Tue, Feb 17, 2004 at 07:01:51PM -, David wrote: > What query would i have to use to search for an item using a polygon as a > parameter? (i.e a very large polygon that would identify smaller polygons > within it) ideally i would like to give postgresq a series of co-ordinates > and then have it return all those results whose polygons fall into that set > of co-ordinates, is this possible? > > at the moment all i can think of is > > > select * from species where location between '(0,0)' and '(1000,0)' and > '(0, 1000)' and '(1000; 1000)'; > > I think im way off, any suggestions? > > Cheers Dave > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > >http://archives.postgresql.org ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] SQL Challenge: Arbitrary Cross-tab
I would use my report writer, but in any case you'd want at least 2 separate queries, maybe three to keep it simple and readable. If you are allowed to use stored procedures you can build up the output by using simple concats instead of text aggregation (which is a procedure of simple concats). Using loops and subqueries you should be to construct the heading (count distinct timekeeper_id) and then select the data row by row concatenating results before you send it out. This is a non-solution which effectively hides the aggregation in a function. Or write it in a client perl app if you must. You can't really do it w/o loops or aggregates. (I wish (hope?) I were wrong about this.) --elein On Tue, Aug 17, 2004 at 07:55:11PM -0700, Josh Berkus wrote: > Folks, > > I have a wierd business case. Annoyingly it has to be written in *portable* > SQL92, which means no arrays or custom aggregates. I think it may be > impossible to do in SQL which is why I thought I'd give the people on this > list a crack at it. Solver gets a free drink/lunch on me if we ever meet at > a convention. > > The Problem: for each "case" there are from zero to eight "timekeepers" > authorized to work on the "case", out of a pool of 150 "timekeepers". This > data is stored vertically: > > authorized_timekeepers: > case_id | timekeeper_id > 213447| 047 > 132113| 021 > 132113| 115 > 132113| 106 > etc. > > But, a client's e-billing application wants to see these timekeepers displayed > in the following horizontal format: > > case_id | tk1 | tk2 | tk3 | tk4 | tk5 | tk6 | tk7 | tk8 > 213447| 047 | | | | | | | | > 132113 | 021 | 115 | 106 | 034 | 109 | 112 | 087 | > etc. > > Order does not matter for timekeepers 1-8. > > This is a daunting problem because traditional crosstab solutions do not work; > timekeepers 1-8 are coming out of a pool of 150. > > Can it be done? Or are we going to build this with a row-by-row procedural > loop? (to reiterate: I'm not allowed to use a custom aggregate or other > PostgreSQL "advanced feature") > > -- > Josh Berkus > Aglio Database Solutions > San Francisco > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] UPDATEABLE VIEWS ... Examples?
There is a write up on these at: http://www.varlena.com/GeneralBits/82.php --elein [EMAIL PROTECTED]Varlena, LLCwww.varlena.com PostgreSQL Consulting, Support & Training PostgreSQL General Bits http://www.varlena.com/GeneralBits/ = I have always depended on the [QA] of strangers. On Thu, Jun 16, 2005 at 06:05:03PM -0300, Marc G. Fournier wrote: > > Reading through the docs, both the CREATE VIEW and CREATE RULE pages refer > to how you can use a RULE to 'simulate' an updateable VIEW ... but I can't > seem to find any examples of this ... > > Does anyone know of an online example of doing this that I can read > through? > > Thanks ... > > > Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) > Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 > > ---(end of broadcast)--- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] how to create rule as on delete
I think you want a delete trigger which does your insert and then follows through with the delete by returning old. --elein [EMAIL PROTECTED] On Tue, Oct 18, 2005 at 09:43:34PM -0700, efa din wrote: > This is my rule for doing the delete event. The rule > can be created. But the problem is, after the record > has been deleted from the 'mytable', this record > cannot be inserted into table 'maytable_log'. > > CREATE RULE on_delete AS ON DELETE TO mytable DO > INSERT INTO mytable_log values (old.id,old.name); > > > > If I add the DO INSTEAD,the record can be inserted > into 'maytable_log' and also still remain in the table > 'maytable'. Which is exactly not exist at all. It just > show it as im using the DO INSTEAD. > > CREATE RULE on_delete AS ON DELETE TO mytable DO > INSTEAD INSERT INTO mytable_log values > (old.id,old.name); > > My problem is, how to insert the deleted record into > table 'mytable_log' without showing it in table > 'maytable'. I really need the solution..please > > > > > > > __ > Yahoo! Mail - PC Magazine Editors' Choice 2005 > http://mail.yahoo.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 > ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] "large" IN/NOT IN subqueries result in query returning wrong data
On Tue, Dec 27, 2005 at 02:44:51PM -0500, Tom Lane wrote: > "George Pavlov" <[EMAIL PROTECTED]> writes: > > select count(*) from t2 where t2.name not in ( > > select t1.name from t1 limit 261683) > > --> 13 > > select count(*) from t2 where t2.name not in ( > > select t1.name from t1 limit 261684) > > --> 0 > > > What is so magical about 261683? > > Most likely, the 261684'th row of t1 has a NULL value of name. > Many people find the behavior of NOT IN with nulls unintuitive, > but it's per SQL spec ... > > regards, tom lane In 8.0 we get: elein=# select 1 in (NULL, 1, 2); ?column? -- t (1 row) elein=# select 3 not in (NULL, 1, 2); ?column? -- (1 row) For consistency, either both should return NULL or both return true/false. For completeness testing, the following are correct. Select NULL in/not in any list returns NULL. elein=# select NULL in (1,2); ?column? -- (1 row) elein=# select NULL not in (1,2); ?column? -- (1 row) elein=# select NULL in (NULL, 1,2); ?column? -- (1 row) elein=# select NULL not in (NULL, 1,2); ?column? -- (1 row) elein -- [EMAIL PROTECTED]Varlena, LLCwww.varlena.com (510)655-2584(o) (510)543-6079(c) PostgreSQL Consulting, Support & Training PostgreSQL General Bits http://www.varlena.com/GeneralBits/ -- AIM: varlenallc Yahoo: AElein Skype: varlenallc -- I have always depended on the [QA] of strangers. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] "large" IN/NOT IN subqueries result in query returning wrong data
On Tue, Dec 27, 2005 at 07:25:40PM -0500, Tom Lane wrote: > elein <[EMAIL PROTECTED]> writes: > > In 8.0 we get: > > >elein=# select 1 in (NULL, 1, 2); > > ?column? > >-- > > t > >(1 row) > > >elein=# select 3 not in (NULL, 1, 2); > > ?column? > >-- > > >(1 row) > > > For consistency, either both should return NULL or > > both return true/false. > > The above behavior is correct per spec. Feel free to argue its > consistency with the SQL committee ;-) Oh, no! Not the committee! > > Note that the above are not inverses because you changed the lefthand > input. You do get consistent results when you just add or omit NOT: Yes, you are right. I skipped the permutations to get down to the point. > > regression=# select 1 not in (NULL, 1, 2); > ?column? > -- > f > (1 row) > > regression=# select 3 in (NULL, 1, 2); > ?column? > -- > > (1 row) > > regards, tom lane Thanks for your clarification. ~elein [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Returning String as Integer
Use the to_number() function to convert text to numbers. In the manual under functions and operators. The other function like it is to_date(). --elein [EMAIL PROTECTED] On Fri, May 05, 2006 at 02:37:13PM -0700, Kashmira Patel (kupatel) wrote: > Hi all, >I have a table with a column of type 'text'. It mainly contains numbers. Is > there any way to select a value from this column and return it as an integer? > > Thanks, > Kashmira ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
