Re: [SQL] locks and variable substitution

[Eric Clark]

On Fri, 2003-07-25 at 11:49, [EMAIL PROTECTED] wrote:
> 
> lock table excl_table in exclusive mode;

That probably wont work, but this will:

EXECUTE ''LOCK TABLE '' 
   || quote_ident(excl_table) || '' IN EXCLUSIVE MODE'';

Eric


---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [SQL] One to many query question

[Eric Clark]

On Wed, 2003-07-30 at 12:35, Dave Dribin wrote: 
> CREATE TABLE cd (
>   id integer unique,
>   artist varchar(25),
>   title varchar(25)
> );
> 
> CREATE TABLE cd_genres (
>   cd_id integer,
>   genre varchar(25)
> );

I think you've got this backwards.  There is no advantage in the above
table's over simply having a genre varchar(25) in the cd table.

You really want:

CREATE TABLE genre (
genre_id serial,
genre varchar(25)
);

CREATE TABLE cd (
cd_id integer unique,
artist varchar(25),
title varchar(25),
genre_id varchar(25) references genre (genre_id)
);

> How do I write a query to find all CDs that are NOT Rock?  A co-worker
> showed me the following query:

Now the query is simple:

SELECT cd.*, genre.genre FROM cd, genre WHERE cd.genre_id =
genre.genre_id AND genre.genre != 'Rock';

Hope that helps,
Eric


---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [SQL] One to many query question

[Eric Clark]

> This doesn't allow multiple genre's per CD, though, does it?  A CD
> can only have 1 genre_id.  I would like the ability to have multiple
> genres, in which case a third table is necessary:
> 
> CREATE TABLE cd_genres (
> cd_id integer,
> genre_id integer
> );
> 
> cd_id references cd.id and genre_id references genre.genre_id.
> 
> This still requires the complex LEFT JOIN query from my first post,
> too, I think, *plus* an extra join between cd_genres and genre.

Sorry, the cd_genre table would be the way to do it.  This was a fairly
complex problem so I created the tables in a test database and wrote a
few queries that I think solve the problem for you, depending on how you
want select to return the genre list.

Here's a couple queries that will only get cd's that are not part of
Rock.

SELECT c.*
FROM cd AS c
WHERE 'Rock' NOT IN (SELECT g.genre FROM genre AS g, cd_genre AS cg
WHERE g.genre_id = cg.genre_id AND cg.cd_id = c.cd_id);

SELECT c.*
FROM cd AS c
WHERE NOT EXISTS (SELECT NULL FROM genre AS g, cd_genre AS cg WHERE
g.genre_id = cg.genre_id AND cg.cd_id = c.cd_id AND g.genre = 'Rock');

The second one should be faster.  The next query will show all cd's that
not exclusively Rock. (OK Computer should be in the result)

SELECT c.*
FROM cd AS c, cd_genre AS cg, genre AS g
WHERE c.cd_id = cg.cd_id
AND cg.genre_id = g.genre_id
AND g.genre != 'Rock';

If you add g.genre to any of the above queries you will get one row per
cd+genre combination.  I dont know of any way to make that a delimited
list other than writing a function.  So I wrote one for fun.  The
argument is the cd_id.

CREATE OR REPLACE FUNCTION genre_list (integer) RETURNS TEXT AS '
DECLARE
cdid ALIAS FOR $1;
return_val TEXT;
r RECORD;
BEGIN
FOR r IN SELECT g.genre 
   FROM genre AS g, cd_genre AS cg 
  WHERE g.genre_id = cg.genre_id AND cg.cd_id = cdid LOOP
IF return_val IS NULL THEN
return_val := r.genre;
ELSE
return_val := return_val || '', '' || r.genre;
END IF;
END LOOP;
RETURN return_val;
END
' LANGUAGE 'plpgsql' STABLE RETURNS NULL ON NULL INPUT SECURITY INVOKER;

Now I see that while composing this email some others have replied with
more simple solutions.  For your case I prefer the added tables as they
enforce the possible list of genre's.  Its also handy to keep them
seperate to get the list of genre's to display in a UI.

Eric

ps: aliasing all the table names is just my habit, do it however you see
fit.  I also dont like to make my table names plural, its implied.



---(end of broadcast)---
TIP 8: explain analyze is your friend