Thread: Function as Phantom Field

I have a database like this:

CREATE TABLE articles (
         article_id      serial primary key,
         title           text
);

CREATE TABLE pages (
         article_id      integer,
         page            text
);

And I want to be able to do a

SELECT article_id, title, count_pages(article_id) FROM articles

Now, I have already written the count_pages function (it's just a count(*)
where article_id = $1) but I don't know how to do the SELECT in one pass,
is there some way I can do this with triggers or is there a special field
name I can use to specify the current article_id as the function argument?

Charles Tassell <ctassell@isn.net> writes:

> I have a database like this:
>
> CREATE TABLE articles (
>          article_id      serial primary key,
>          title           text
> );
>
> CREATE TABLE pages (
>          article_id      integer,
>          page            text
> );
>
> And I want to be able to do a
>
> SELECT article_id, title, count_pages(article_id) FROM articles
>
> Now, I have already written the count_pages function (it's just a count(*)
> where article_id = $1) but I don't know how to do the SELECT in one pass,
> is there some way I can do this with triggers or is there a special field
> name I can use to specify the current article_id as the function argument?

I'm not sure I understand what you want to do, but maybe a GROUP BY is
what you're looking for:

SELECT article_id, title, count_pages(article_id)
FROM articles
GROUP BY article_id, title


Tomas

>>>>> "CT" == Charles Tassell <ctassell@isn.net> writes:

 CT> I have a database like this:
 CT> CREATE TABLE articles (
 CT> article_id      serial primary key,
 CT> title           text
 CT> );

 CT> CREATE TABLE pages (
 CT> article_id      integer,
 CT> page            text
 CT> );

 CT> And I want to be able to do a

 CT> SELECT article_id, title, count_pages(article_id) FROM articles

 CT> Now, I have already written the count_pages function (it's just a count(*)
 CT> where article_id = $1) but I don't know how to do the SELECT in one pass,
 CT> is there some way I can do this with triggers or is there a special field
 CT> name I can use to specify the current article_id as the function argument?

Try this:

select articles.article_id, count(*) from articles, pages where
article.article_id = pages.article_id group by pages.article_id;

--
Anatoly K. Lasareff              Email:       tolik@aaanet.ru