[SQL] Cursors and recursion
Hi, I've been trying to do recursion and cursors in PL/PgSQL (PostgreSQL 8.1). It's a function who has a cursor and calls itself, but the problem raises after the first recursion, when PgSQL complains: ERROR: cursor "cur" already in use Are the cursors kept globally? or cached like TEMP TABLE? There's an example code available in http://www.croata.cl/cur.sql . In fact, it is an abstraction of the real stored procedure where the problem occurs. -- Thanks in advance, Cro
[SQL] instead of trigger in pg
Hi,
I would like to implement a 1:n relation between two
tables.
An auto-generated number should be used as primary key
which connects both tables.
Example:
table 1:
name | number (prim_key)
table 2:
country | number_table1 (foreign key)
View:
number_table1 | name | country
I would like to insert into both tables in one step.
"Insert into view (name,
country)values('name','country');"
Oracle supports an instead-of-trigger but Postgres
doesn't do this.
CREATE or replace FUNCTION insertInto() RETURNS
trigger AS '
BEGIN
insert into table1(name)values(NEW.name);
insert into
table2(number,country)values(NEW.number,NEW.country);
RETURN NEW;
END;
' LANGUAGE plpgsql;
CREATE TRIGGER insert_TR BEFORE INSERT OR UPDATE ON
view
FOR EACH ROW EXECUTE PROCEDURE insertInto();
I would like to implement something like this but I
don't know how to do.
Thank you for you help!
Jakob
___
Telefonate ohne weitere Kosten vom PC zum PC: http://messenger.yahoo.de
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] "large" IN/NOT IN subqueries result in query returning wrong data
elein <[EMAIL PROTECTED]> writes: > > 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. Remember that NULL means "unknown". So "1 IN (1,2,NULL)" *should* be true because regardless of what that unknown value is it's still obvious that 1 really is in the list. And "3 NOT IN (1,2,NULL)" is unknown because it depends on whether that unknown quantity is 3 or not. IN is the same as "= ANY" so "1 IN (1,2,NULL)" is the same as "1=1 OR 1=2 OR 1=NULL" which is true even though the last of the three is null. -- greg ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Cursors and recursion
Don Croata <[EMAIL PROTECTED]> writes: > It's a function who has a cursor and calls itself, but the problem raises > after the first recursion, when PgSQL complains: > ERROR: cursor "cur" already in use > Are the cursors kept globally? or cached like TEMP TABLE? Cursor names are global within a particular session. IIRC there is a syntax for opening a cursor without specifying a name, in which case plpgsql will pick one that's not in use. This is probably what you want to use. 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] instead of trigger in pg
On 12/28/05, J Crypter <[EMAIL PROTECTED]> wrote:
> Hi,
>
> I would like to implement a 1:n relation between two
> tables.
> An auto-generated number should be used as primary key
> which connects both tables.
> Example:
>
> table 1:
> name | number (prim_key)
>
> table 2:
> country | number_table1 (foreign key)
>
> View:
> number_table1 | name | country
>
> I would like to insert into both tables in one step.
> "Insert into view (name,
> country)values('name','country');"
>
> Oracle supports an instead-of-trigger but Postgres
> doesn't do this.
>
postgres uses rules for this situation... and yes there are INSTEAD OF rules...
> CREATE or replace FUNCTION insertInto() RETURNS
> trigger AS '
>BEGIN
>insert into table1(name)values(NEW.name);
>insert into
> table2(number,country)values(NEW.number,NEW.country);
>
>RETURN NEW;
>END;
> ' LANGUAGE plpgsql;
>
> CREATE TRIGGER insert_TR BEFORE INSERT OR UPDATE ON
> view
>FOR EACH ROW EXECUTE PROCEDURE insertInto();
>
>
> I would like to implement something like this but I
> don't know how to do.
>
> Thank you for you help!
>
> Jakob
>
>
>
>
>
>
> ___
> Telefonate ohne weitere Kosten vom PC zum PC: http://messenger.yahoo.de
>
> ---(end of broadcast)---
> TIP 6: explain analyze is your friend
>
--
Atentamente,
Jaime Casanova
(DBA: DataBase Aniquilator ;)
---(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] Cursors and recursion
On Wed, Dec 28, 2005 at 10:48:25AM -0500, Tom Lane wrote: > Don Croata <[EMAIL PROTECTED]> writes: > > It's a function who has a cursor and calls itself, but the problem raises > > after the first recursion, when PgSQL complains: > > > ERROR: cursor "cur" already in use > > > Are the cursors kept globally? or cached like TEMP TABLE? > > Cursor names are global within a particular session. > > IIRC there is a syntax for opening a cursor without specifying a name, > in which case plpgsql will pick one that's not in use. This is probably > what you want to use. Or, if possible, use "FOR record_or_row IN query LOOP" instead of an explicit cursor. http://www.postgresql.org/docs/8.1/interactive/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING -- Michael Fuhr ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Cursors and recursion
Please, if someone recalls a link, book, piece of code or anything with info about this technique for PL/PgSQL (8.1), please let us know. We've been searching into google, groups.google, http://archives.postgresql.org and http://www.postgresql.org/docs/8.1/interactive with no results. Most of the answers are related to unclosed cursors for the "ERROR: cursor ... already in use" message. Here's the sample code for this issue (i.e. error because cursor already defined in recursive PL/PgSQL function) at: http://www.croata.cl/cur.sql -- Thanx again, Cro On 12/28/05, Tom Lane <[EMAIL PROTECTED]> wrote: Don Croata <[EMAIL PROTECTED]> writes:> It's a function who has a cursor and calls itself, but the problem raises > after the first recursion, when PgSQL complains:> ERROR: cursor "cur" already in use> Are the cursors kept globally? or cached like TEMP TABLE?Cursor names are global within a particular session. IIRC there is a syntax for opening a cursor without specifying a name,in which case plpgsql will pick one that's not in use. This is probablywhat you want to use. regards, tom lane
[SQL] DISTINCT with NULLs and INT fields
What does The SQL Standard say about this one? create table foo (a varchar, b int); insert into foo (a, b) select null, null from bar; -- no problem insert into foo (a, b) select distinct null, null from bar; -- ERROR: column "b" is of type integer but expression is of type text -- HINT: You will need to rewrite or cast the expression. It seems that applying DISTINCT makes the NULL be of type TEXT... I just tried the exact same thing in Ms. SQL Server 2005 and it works with no errors. Looks like SQL Server makes the NULLs be INTs, but is probably better at doing the implicit conversion from INT. Any thoughts on what the "correct" behavior should be here? George ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] DISTINCT with NULLs and INT fields
"George Pavlov" <[EMAIL PROTECTED]> writes:
> What does The SQL Standard say about this one?
> insert into foo (a, b) select distinct null, null from bar;
> -- ERROR: column "b" is of type integer but expression is of type text
According to the SQL spec that query is illegal on its face --- the spec
does not allow a bare NULL keyword except in contexts where the datatype
is immediately evident. You'd have had to write
SELECT DISTINCT CAST(NULL AS varchar), CAST(NULL AS int)
to be fully spec-conformant.
Postgres handles NULL the same way as untyped literals ('foo'), so we
tend to prefer resolving to type text when things are ambiguous. In
this case the DISTINCT operation forces a type choice (if you don't know
what type the data is, you can hardly decide whether two values are equal
or not) and so by the time the INSERT sees it, it looks like a SELECT
of two text columns.
> Looks like SQL Server makes the NULLs be INTs, but is probably
> better at doing the implicit conversion from INT.
"Better" is in the eye of the beholder. It surprises me not at all that
Microsoft would be lax about implicit coercions, but that doesn't make
it a good idea to coerce anything to anything else without complaint.
You might as well not have a type system at all, if you're going to
destroy its ability to detect mistakes that way.
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] DISTINCT with NULLs and INT fields
> "Better" is in the eye of the beholder.
sorry for the value-laden term. "laxer" is more appropriate, of course!
the funny thing is that had they cast the NULLs to TEXT it would have
failed there too (they do not do implicit TEXT to INT).
> It surprises me not at all that
> Microsoft would be lax about implicit coercions, but that doesn't make
> it a good idea to coerce anything to anything else without complaint.
> You might as well not have a type system at all, if you're going to
> destroy its ability to detect mistakes that way.
indeed! but, wait, doesn't our favorite dbms do some implicit casting
too? continuing with my table foo (a varchar, b int):
test=# delete from foo;
DELETE 2
test=# insert into foo values (4,4);
INSERT 0 1
test=# insert into foo values ('4','4');
INSERT 0 1
test=# insert into foo values ('oh','no');
ERROR: invalid input syntax for integer: "no"
test=# select * from foo;
a | b
---+---
4 | 4
4 | 4
(2 rows)
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] DISTINCT with NULLs and INT fields
"George Pavlov" <[EMAIL PROTECTED]> writes:
> indeed! but, wait, doesn't our favorite dbms do some implicit casting
> too? continuing with my table foo (a varchar, b int):
> test=# delete from foo;
> DELETE 2
> test=# insert into foo values (4,4);
> INSERT 0 1
> test=# insert into foo values ('4','4');
> INSERT 0 1
> test=# insert into foo values ('oh','no');
> ERROR: invalid input syntax for integer: "no"
Sure, but in this example the required type of the value is clear from
immediate context (ie, the INSERT). This is one of the cases where
the SQL spec requires a bare NULL to be accepted. (BTW, 'no' and '4'
in this example are *not* values of type text; they are untyped literals
which we eventually resolve as varchar or int.)
The other case you mentioned is one where we are going out on a limb a
bit:
INSERT INTO foo SELECT NULL, NULL;
In this case we allow the SELECT to not force a type choice, so that the
INSERT sees the raw untyped values, but I think that this query is
probably illegal per spec --- I believe the spec requires a SELECT to
deliver well-defined data types. Too lazy to go look up chapter and
verse at the moment.
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] DISTINCT with NULLs and INT fields
> Sure, but in this example the required type of the value is clear from > immediate context (ie, the INSERT). This is one of the cases where > the SQL spec requires a bare NULL to be accepted. (BTW, 'no' and '4' > in this example are *not* values of type text; they are > untyped literals which we eventually resolve as varchar or int.) hmmm... with the risk of boring everyone to tears: test=# insert into foo values (4::int,4::int); INSERT 0 1 test=# insert into foo values (4::text,4::text); ERROR: column "b" is of type integer but expression is of type text HINT: You will need to rewrite or cast the expression. test=# insert into foo values (cast(4 as int),cast(4 as int)); INSERT 0 1 test=# insert into foo values (cast(4 as text),cast(4 as text)); ERROR: column "b" is of type integer but expression is of type text HINT: You will need to rewrite or cast the expression. (i actually think it is important to understand how this stuff works and thanks for explaining!) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] DISTINCT with NULLs and INT fields
George Pavlov wrote: > test=# insert into foo values (4::int,4::int); > INSERT 0 1 > test=# insert into foo values (4::text,4::text); > ERROR: column "b" is of type integer but expression is of type text > HINT: You will need to rewrite or cast the expression. > test=# insert into foo values (cast(4 as int),cast(4 as int)); > INSERT 0 1 > test=# insert into foo values (cast(4 as text),cast(4 as text)); > ERROR: column "b" is of type integer but expression is of type text > HINT: You will need to rewrite or cast the expression. The last sentence (and the second one as well -- they are exactly equivalent) exemplifies that there isn't an implicit typecase from text to integer. If you use single quotes instead of an explicit cast, the literal is assumed to be of type "unknown", which _can_ be casted automatically to integer. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(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
[SQL] Help with simple query
I have a simple table called notes which contains notes for users. The table has 4 columns: note_id (auto-incrementing primary key), user_id (foreign key to a users table), note (varchar), and modified_date (timestamp). Is there a nice simple query I can run that will return me a list of all the *latest* notes for all users (users can have many notes in the table)? I'm trying to figure out a simple way of doing it but seem to be having some mental block or there is no easy way to do it. The following query will return me all the latest dates, but I can't return the note_id or subject with it. SELECT n.user_id, max(n.modified_date) FROM notes n GROUP by n.user_id ORDER BY n.user_id Is this simpler than I am making it? Regards, Collin ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Help with simple query
Collin Peters <[EMAIL PROTECTED]> writes: > Is there a nice simple query I can run that will return me a list of > all the *latest* notes for all users (users can have many notes in the > table)? You can use SELECT DISTINCT ON for that, if you don't mind using a Postgres-only feature. See the "weather reports" example in the SELECT reference page. If you want to stick to portable SQL, you can still do it, but it's pretty ugly and slow. Look in the list archives for previous discussions. regards, tom lane ---(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] Help with simple query
At 06:58 PM 12/28/05, Collin Peters wrote: The following query will return me all the latest dates, but I can't return the note_id or subject with it. SELECT n.user_id, max(n.modified_date) FROM notes n GROUP by n.user_id ORDER BY n.user_id Is this simpler than I am making it? No, it's not "simple". You need to join the results of above sql back to the original table (and relocate the order by clause): SELECT notes.note_id, notes.user_id, maxx.max_date, notes.note FROM (SELECT n.user_id, max(n.modified_date) AS max_date FROM notes n GROUP by n.user_id) AS maxx JOIN notes on notes.user_id = maxx.user_id AND notes.modified_date = maxx.max_date ORDER BY notes.user_id; ---(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] Help with simple query
If you want the latest by user, you can cheat a bit and use the fact that the id's are incrementing, thus ordering by the id is about the same as ordering by the date field. I know it can be inexact in some corner cases, but it's a good approximation, and very useful in practice : SELECT user_id, max(note_id) FROM notes GROUP by user_id; So : SELECT * FROM notes WHERE id IN (SELECT max(note_id) FROM notes GROUP by user_id) ; Can postgres use the index on these max() clauses now ? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Help with simple query
or, from the "stupid tricks" category: SELECT n.user_id, max(lpad(extract(epoch from n.modified_date),12,'0') || ' ' || note) FROM notes n GROUP by n.user_id i am not *really* suggesting this! ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] DISTINCT with NULLs and INT fields
> > test=# insert into foo values (4::int,4::int); > > INSERT 0 1 > > test=# insert into foo values (4::text,4::text); > > ERROR: column "b" is of type integer but expression is of type text > > HINT: You will need to rewrite or cast the expression. > > test=# insert into foo values (cast(4 as int),cast(4 as int)); > > INSERT 0 1 > > test=# insert into foo values (cast(4 as text),cast(4 as text)); > > ERROR: column "b" is of type integer but expression is of type text > > HINT: You will need to rewrite or cast the expression. > > The last sentence (and the second one as well -- they are exactly > equivalent) exemplifies that there isn't an implicit typecase > from text > to integer. If you use single quotes instead of an explicit cast, the > literal is assumed to be of type "unknown", which _can_ be casted > automatically to integer. yes, #1 is the same as #3, and #2 is the same as #4. i was trying to provide a counter example to tom's statement that pgsql does not do type coercions. the interesting thing here is that 4::int gets into a text field whereas 4::text does not get into an integer field. seems to me like there is an implicit int-to-text cast (without a symmetrical text-to-int one) unless 4::int is somehow *still* considered an 'untyped literal' (4 and '4' seem untyped to me, but 4::int does not)?? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Cursors and recursion
On Wed, Dec 28, 2005 at 04:37:21PM -0300, Don Croata wrote: > Please, if someone recalls a link, book, piece of code or anything with info > about this technique for PL/PgSQL (8.1), please let us know. We've been > searching into google, groups.google, http://archives.postgresql.org and > http://www.postgresql.org/docs/8.1/interactive with no results. Most of the > answers are related to unclosed cursors for the "ERROR: cursor ... already > in use" message. See the "Cursors" section of the PL/pgSQL documentation and read about unbound cursors: http://www.postgresql.org/docs/8.1/interactive/plpgsql-cursors.html "Note: A bound cursor variable is initialized to the string value representing its name, so that the portal name is the same as the cursor variable name, unless the programmer overrides it by assignment before opening the cursor. But an unbound cursor variable defaults to the null value initially, so it will receive an automatically-generated unique name, unless overridden." For example, instead of DECLARE cur CURSOR FOR SELECT ...; BEGIN OPEN cur; use DECLARE cur refcursor; BEGIN OPEN cur FOR SELECT ...; But as I mentioned in a previous post, it's usually easier to use FOR-IN-LOOP. -- Michael Fuhr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] DISTINCT with NULLs and INT fields
"George Pavlov" <[EMAIL PROTECTED]> writes: > the interesting thing here is that 4::int gets into a text > field whereas 4::text does not get into an integer field. seems to me > like there is an implicit int-to-text cast (without a symmetrical > text-to-int one) Yeah, there is. You can easily see the list of implicit casts for yourself: select castsource::regtype, casttarget::regtype from pg_cast where castcontext = 'i' and castsource != casttarget; There's 90 of them in current CVS tip, and most of 'em are unsurprising within-type-category casts, such as implicit promotion of int to bigint. However we have about a dozen implicit casts to text from the numeric and datetime type categories. Personally I would dearly love to get rid of these, because they are accidents waiting to happen (and they do regularly bite people, see the mail list archives for evidence). But it seems people expect to be able to do things like number || ' string' without explicitly casting the number to text. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] constraint and ordered value
On Wed, Dec 28, 2005 at 00:52:18 +0700, David Garamond <[EMAIL PROTECTED]> wrote: > Is it possible to use only CHECK constraint (and not triggers) to > completely enforce ordered value of a column (colx) in a table? By that > I mean: > > 1. Rows must be inserted in the order of colx=1, then colx=2, 3, and so on; > > 2. When deleting (or updating), "holes" must not be formed, e.g. if > there are three rows then row with colx=3 must be the first one deleted, > and then colx=2 the second, and so on. > > I can see #1 being accomplished using a NOT NULL + UNIQUE constraint and > a CHECK constraint that calls some PL function where the function does a > simple checking (new.colx=1 if COUNT(colx)==0, or new.colx=MAX(colx)+1). > > But is it possible to do #2 using only constraints? No. A constraint only applies to one row at a time. If you try to work around this by calling a function that does queries it isn't guarenteed to work. And if you are thinking of calling a function that does a query, you aren't looking at saving time over using triggers. Also, if you are going to have concurrent updates, you are going to need to do table locking to make this work. ---(end of broadcast)--- TIP 6: explain analyze is your friend
