FAQ
Good morning,

I have been setting up a server at home as a workbench to learn PHP,
Postgres, and a few other tools. I recently installed an application
(PHPRecipeBook) that was written for Postgres 7.2.3. But I have 8.2.3
installed. When I try to access the recipe book, I get a few errors;
surprise, surprise. Is there any way to identify what adjustments I need
to make in the queries to compensate for the difference in target
versions? My dream option would be a form in which I enter the original
target and the new target and get back a list of all the syntax changes
I need to account for. I started to walk through the release notes, but
there are far too many changes spread too widely to make sense of.

The first level errors I get are:

SELECT count(*) FROM recipe_recipes WHERE recipe_name LIKE 'A%' OR
recipe_name LIKE 'a%' ORDER BY recipe_name
ERROR: column "recipe_recipes.recipe_name" must appear in the GROUP BY
clause or be used in an aggregate function

SELECT recipe_id,recipe_name FROM recipe_recipes WHERE recipe_name LIKE
'A%' OR recipe_name LIKE 'a%' ORDER BY recipe_name LIMIT OFFSET 0
ERROR: syntax error at or near "OFFSET" LINE 1: ...R recipe_name LIKE
'a%' ORDER BY recipe_name LIMIT OFFSET 0 ^

I fully expect there will be more after I get these corrected. My setup
is as follows:

Slackware 10.2
Apache 1.3.37
PHP 4.4.5
PostgreSQL 8.2.3

Thank you,

Bob McConnell
N2SPP

Search Discussions

  • Sean Davis at Mar 31, 2007 at 10:23 pm

    Bob McConnell wrote:
    Good morning,

    I have been setting up a server at home as a workbench to learn PHP,
    Postgres, and a few other tools. I recently installed an application
    (PHPRecipeBook) that was written for Postgres 7.2.3. But I have 8.2.3
    installed. When I try to access the recipe book, I get a few errors;
    surprise, surprise. Is there any way to identify what adjustments I
    need to make in the queries to compensate for the difference in target
    versions? My dream option would be a form in which I enter the
    original target and the new target and get back a list of all the
    syntax changes I need to account for. I started to walk through the
    release notes, but there are far too many changes spread too widely to
    make sense of.

    The first level errors I get are:

    SELECT count(*) FROM recipe_recipes WHERE recipe_name LIKE 'A%' OR
    recipe_name LIKE 'a%' ORDER BY recipe_name
    ERROR: column "recipe_recipes.recipe_name" must appear in the GROUP BY
    clause or be used in an aggregate function
    I'm not sure, but I don't think the statement was ever correct SQL. In
    any case, you would need to add a GROUP BY clause.
    SELECT recipe_id,recipe_name FROM recipe_recipes WHERE recipe_name
    LIKE 'A%' OR recipe_name LIKE 'a%' ORDER BY recipe_name LIMIT OFFSET 0
    ERROR: syntax error at or near "OFFSET" LINE 1: ...R recipe_name LIKE
    'a%' ORDER BY recipe_name LIMIT OFFSET 0 ^
    Again, I don't think this was ever correct.
    I fully expect there will be more after I get these corrected. My
    setup is as follows:
    I haven't used phprecipebook before, but it looks it is generating quite
    incorrect SQL queries. If it was truly written for version 7.2.3, that
    means that it probably hasn't been updated in several years, so you
    might want to look seriously at what phprecipebook offers compared to
    alternatives. I really know nothing about the software, so I could be
    very off base, but the SQL errors you are getting are pretty basic.

    Sean
  • Bob McConnell at Mar 31, 2007 at 11:09 pm

    Sean Davis wrote:
    Bob McConnell wrote:
    Good morning,


    The first level errors I get are:

    SELECT count(*) FROM recipe_recipes WHERE recipe_name LIKE 'A%' OR
    recipe_name LIKE 'a%' ORDER BY recipe_name
    ERROR: column "recipe_recipes.recipe_name" must appear in the GROUP BY
    clause or be used in an aggregate function
    I'm not sure, but I don't think the statement was ever correct SQL. In
    any case, you would need to add a GROUP BY clause.
    SELECT recipe_id,recipe_name FROM recipe_recipes WHERE recipe_name
    LIKE 'A%' OR recipe_name LIKE 'a%' ORDER BY recipe_name LIMIT OFFSET 0
    ERROR: syntax error at or near "OFFSET" LINE 1: ...R recipe_name LIKE
    'a%' ORDER BY recipe_name LIMIT OFFSET 0 ^
    Again, I don't think this was ever correct.
    I fully expect there will be more after I get these corrected. My
    setup is as follows:
    I haven't used phprecipebook before, but it looks it is generating quite
    incorrect SQL queries. If it was truly written for version 7.2.3, that
    means that it probably hasn't been updated in several years, so you
    might want to look seriously at what phprecipebook offers compared to
    alternatives. I really know nothing about the software, so I could be
    very off base, but the SQL errors you are getting are pretty basic.

    Sean
    Sean,

    Thanks for the help, looks like I have my work cut out for me.
    PHPRecipeBook was written for MySQL, but added Postgres as an apparent
    afterthought. They have not updated the PG side for some time, but they
    claim it did work with 7.2.3. It would appear they have been seduced by
    someone with a bias in the other direction.

    As far as other options, I haven't found any. There are packages with
    fat clients written for KDE (KRecipes) or GNOME (Gourmet) users, but I
    have not found anything useful with a web front end. I'm not sure this
    is yet, but it's all I could find to try out. I just want something
    useful to organize a few thousand family recipes before they are lost.
    My mother just turned 82 and we have most of hers stored on paper. But
    we have already lost a couple of her and dad's sisters with no records
    of theirs. I haven't even begun to talk with my wife's family, and one
    daughter in law whose grandmother and aunts used to operate a catering
    kitchen.

    Thank you,

    Bob McConnell
    N2SPP
  • Tom Lane at Apr 1, 2007 at 6:45 am

    Bob McConnell writes:
    Sean Davis wrote:
    I'm not sure, but I don't think the statement was ever correct SQL. In
    any case, you would need to add a GROUP BY clause.
    Thanks for the help, looks like I have my work cut out for me.
    PHPRecipeBook was written for MySQL, but added Postgres as an apparent
    afterthought. They have not updated the PG side for some time, but they
    claim it did work with 7.2.3. It would appear they have been seduced by
    someone with a bias in the other direction.
    Hm, these examples appear to shed no credit on either PHPRecipeBook or
    MySQL. Some comments:

    * The first query (SELECT count(*) ... ORDER BY with no GROUP BY) is
    surely pretty silly, because an aggregate function without GROUP BY
    is going to yield only one row, so what's the point of ORDER BY?

    It's not legal by my reading of the SQL spec, yet according to my test
    just now mysql 5.0.22 takes it (1 demerit for mysql, maybe 2 because
    this is a recent release and they still haven't fixed it) ... even in
    sql_mode=ansi (make that 3 demerits). Also, pgsql 7.2 certainly didn't
    take it (I checked 7.0 too, the oldest version still alive hereabouts),
    so 1 demerit for PHPRecipeBook who obviously didn't test this query on
    anything but mysql.

    * 'LIMIT OFFSET 0' bombs in both mysql and Postgres, and always has
    AFAIK --- there's supposed to be a numeric argument to the LIMIT.
    1 demerit to PHPRecipeBook for being just plain broken.
    As far as other options, I haven't found any. There are packages with
    fat clients written for KDE (KRecipes) or GNOME (Gourmet) users, but I
    have not found anything useful with a web front end. I'm not sure this
    is yet, but it's all I could find to try out.
    Seems like you need to be prepared to do a bit of work on PHPRecipeBook.
    These things don't sound real hard to fix if you know a little SQL
    ... the real question is can you get upstream to take back the patches?
    It's no fun fixing bugs if you just have to fix 'em again in the next
    release.
    I just want something
    useful to organize a few thousand family recipes before they are lost.
    My mother just turned 82 and we have most of hers stored on paper. But
    we have already lost a couple of her and dad's sisters with no records
    of theirs. I haven't even begun to talk with my wife's family, and one
    daughter in law whose grandmother and aunts used to operate a catering
    kitchen.
    Sounds a bit familiar ... my wife spent a lot of time a few years ago
    trying to organize recipes from her father's and grandfather's bakery
    business. Strangely, she had no interest in putting 'em into a
    database.

    regards, tom lane
  • Bob McConnell at Apr 1, 2007 at 8:25 pm

    Tom Lane wrote:
    Bob McConnell <rmcconne@lightlink.com> writes:
    Seems like you need to be prepared to do a bit of work on PHPRecipeBook.
    These things don't sound real hard to fix if you know a little SQL
    ... the real question is can you get upstream to take back the patches?
    It's no fun fixing bugs if you just have to fix 'em again in the next
    release.
    It has been over a year since this release, and it doesn't look like
    there is any current activity. The last news item was pointing to a new
    package the maintainer was working on, so I don't know if there will be
    any more releases. If I proceed, I will post what I find on the forum at
    Source Forge just in case.
    I just want something
    useful to organize a few thousand family recipes before they are lost.
    My mother just turned 82 and we have most of hers stored on paper. But
    we have already lost a couple of her and dad's sisters with no records
    of theirs. I haven't even begun to talk with my wife's family, and one
    daughter in law whose grandmother and aunts used to operate a catering
    kitchen.
    Sounds a bit familiar ... my wife spent a lot of time a few years ago
    trying to organize recipes from her father's and grandfather's bakery
    business. Strangely, she had no interest in putting 'em into a
    database.
    Probably just a difference in perspective. I see the database as a means
    to enforce consistent content and layout. Add to that the fact that I
    sometimes cannot read my own handwriting, and it begins to make some
    sense. I can also use common export programs to create copies of the
    collection suitable for burning to CD that I can send to any number of
    siblings, cousins and their descendants. So for me the DB is a useful
    tool for this project as long as I don't need to design the schema from
    scratch. She may not see it the same way.

    Thanks for the suggestions. I am adding SQL to the list of topics I need
    to study further. So far I know just enough to examine a few tables
    using the Sybase ISQL tools with ASA. Probably not enough to get into
    trouble yet, but getting there.

    Bob McConnell
    N2SPP

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedMar 31, '07 at 7:46p
activeApr 1, '07 at 8:25p
posts5
users3
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase