Re: [SQL] ORDER BY with LTREE
On Fri, Feb 17, 2006 at 02:28:13PM +, Axel Straschil wrote: > Im working with the ltree [1] datatype and have labels that can not used > directly in ltree and a want to get a tree like strukture ordered by the > labels. Are you saying that you can't convert labels into ltree values like 'c.a' and order by that? What data type are the real labels? One possibility would be to create a custom operator and add a USING clause to the ORDER BY clause. The operator could compare records by whatever algorithm you like, even if that means looking up other records (e.g., to determine an ancestor's label). That could be expensive for a large table but it might be worth considering. -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Given 02-01-2006 to 02-28-2006, output all days.
On Sun, Feb 19, 2006 at 01:47:21PM -0500, Henry Ortega wrote: > I was able to find a suitable 7.3.2 plpgsql.so and now plpgsql works. > (supposedly) > > I am trying out some really basic function creation such as this: > > create function dng2(start_date DATE) returns setof date as $$ > declare > aa date:=start_date; > > But I always get this > ERROR: parser: parse error at or near "DATE" at character 33 > before I can even finish. You're using features (named parameters, dollar quotes) that are available only in 8.0 and later; see the 7.3 documentation for the correct syntax in that version. But as someone else mentioned, do consider upgrading, if not to 8.1.3 or 8.0.7 then at least to 7.3.14. Lots of bugs have been fixed in the three years since 7.3.2 was released, some involving data loss. -- Michael Fuhr ---(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] Given 02-01-2006 to 02-28-2006, output all days.
On Sun, 19 Feb 2006, Henry Ortega wrote: > I was able to find a suitable 7.3.2 plpgsql.so and now plpgsql works. > (supposedly) > > I am trying out some really basic function creation such as this: > > create function dng2(start_date DATE) returns setof date as $$ > declare > aa date:=start_date; I don't think the beginning is a valid function definition in 7.3.x as I'm pretty sure it didn't have the grammar support for named parameters. Also, I think dollar quoting came in 8.0, so that's not going to work either. You may be looking at a different version of the docs than the version you're using. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Given 02-01-2006 to 02-28-2006, output all days.
I was able to find a suitable 7.3.2 plpgsql.so and now plpgsql works. (supposedly)I am trying out some really basic function creation such as this:create function dng2(start_date DATE) returns setof date as $$
declareaa date:=start_date;But I always get thisERROR: parser: parse error at or near "DATE" at character 33before I can even finish.Any idea why this happens?
On 2/17/06, Owen Jacobson <[EMAIL PROTECTED]> wrote:
That usually indicates that, for whatever reason, plpgsql.so is from a different version of PostgreSQL than the database server. If you installed PostgreSQL from source, make sure you configured the server to look in the same lib dir as its libs were installed to; if you've installed from package management of some kind (RPM?) make sure you have the same versions of all postgres-related packages.
You should also upgrade, if possible. 7.3 is effectively obsolete (37 releases old); there are a number of bugfixes and performance improvements in more recent versions.-Owen-Original Message-
From: Henry Ortega [mailto:[EMAIL PROTECTED]]Sent: Friday, February 17, 2006 2:06 PMTo: Owen JacobsonSubject: Re: [SQL] Given 02-01-2006 to 02-28-2006, output all days.
This sounds good. I don't have plpgsql loaded though.I am trying to load plpgsql and it's giving me:ERROR: Load of file /usr/lib/pgsql/plpgsql.so failed: /usr/lib/pgsql/plpgsql.so: undefined symbol: xlateSqlType
createlang: language installation failedI have pgsql 7.3.2I am googling and can't seem to find the answer. Any help would be appreciated.On 2/17/06, Owen Jacobson <
[EMAIL PROTECTED]> wrote:Henry Ortega wrote:(question about set of all days between two dates)I don't know of a builtin way to do it off the top of my head, but it's a pretty simple function to write:
create function days (start date, finish date) returns setof date as $$declare curdate date;begin curdate := start; while (curdate <= finish) loopreturn next curdate;curdate := curdate + 1;
end loop; return;end;$$ language plpgsql;# select * from days ('2006-02-01', '2006-02-07');days2006-02-012006-02-022006-02-032006-02-042006-02-052006-02-06
2006-02-07(7 rows)---(end of broadcast)---TIP 5: don't forget to increase your free space map settings
Re: [SQL] Need help: Find dirty rows, Update, Delete SQL
Am Samstag, 18. Februar 2006 18:41 schrieb [EMAIL PROTECTED]:
> Hello,
>
> I need a bit of help with some SQL.
> I have two tables, call them Page and Bookmark.
> Each row in Page can have many Bookmarks pointing to it, and
> they are joined via a FK (Page.id = Bookmark.page_id).
>
> Page has a 'url' column: Page.url, which has a unique index on it.
>
> My Page.url column got a little dirty, and I need to clean it up,
> and that's what I need help with.
>
> Here is an example of dirtiness:
>
> Page:
>
> id=1 url = 'http://example.com/'
> id=2 url = 'http://example.com/#' -- dirty
> id=3 url = 'http://example.com/#foo' -- dirty
>
> The last two rows are dirty. Normally I normalize URLs before
> inserting them, but these got in, and now I need to clean them.
>
> The problem is that rows in Bookmark table may point to dirty
> rows in Page, so I can't just remove the dirty rows, and I can't
> just update 'url' column in Page to 'http://example.com/',
> because that column is unique.
>
> Is there some fancy SQL that I can use them to find the dirty
> rows in page (... where url like '%#%') and then find rows in
> Bookmark table that point to them, then point those rows to
> good rows in Page (e.g. id=1 row above), and finally remove the
> dirty rows from Page?
try this. But please check if it really does its job. I just wrote it down in
a minute or two. There will be an easier way or nicer written SQL but a sit
is just a one time operation you shoudn't care too much. One more hint: you
should add a CHECK clause to your page_url like "page_url text NOT NULL
UNIQUE CHECK (page_url !~ '#')"
here is my test code
CREATE TABLE pages (
page_id SERIAL PRIMARY KEY,
page_url text NOT NULL UNIQUE
);
CREATE TABLE bookmarks (
bm_id SERIAL PRIMARY KEY,
bm_text text not null,
page_id int4 NOT NULL REFERENCES pages (page_id)
);
INSERT INTO pages (page_url) VALUES ('http://example.com/');
INSERT INTO pages (page_url) VALUES ('http://example.com/#');
INSERT INTO pages (page_url) VALUES ('http://example.com/#foo');
INSERT INTO pages (page_url) VALUES ('http://example2.com/#foo');
INSERT INTO pages (page_url) VALUES ('http://example3.com/#foobar');
insert into bookmarks (bm_text, page_id) values ('test1', 1);
insert into bookmarks (bm_text, page_id) values ('test2', 1);
insert into bookmarks (bm_text, page_id) values ('test3', 2);
insert into bookmarks (bm_text, page_id) values ('test4', 2);
insert into bookmarks (bm_text, page_id) values ('test5', 3);
insert into bookmarks (bm_text, page_id) values ('test6', 3);
insert into bookmarks (bm_text, page_id) values ('test7', 4);
BEGIN;
UPDATE bookmarks set page_id = pages2.page_id
FROM
pages AS pages1,
pages AS pages2
WHERE
pages1.page_id = bookmarks.page_id
AND pages2.page_url = split_part(pages1.page_url, '#', 1)
;
DELETE FROM pages WHERE page_id IN (
SELECT
pages1.page_id
FROM
pages AS pages1
JOIN pages AS pages2 ON (
pages1.page_id != pages2.page_id
AND pages2.page_url = split_part(pages1.page_url, '#', 1)
)
WHERE position('#' in pages1.page_url) > 0
AND pages1.page_id NOT IN (SELECT page_id FROM bookmarks)
);
;
UPDATE pages SET page_url = split_part(page_url, '#', 1)
WHERE position('#' in pages.page_url) > 0
;
select * from bookmarks;
select * from pages;
COMMIT;
kind regards,
janning
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
