Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Posts
    3

    Unanswered: BOOLEAN in language SQL ??

    Newbie to PostgreSQL, not entirely new to databases.

    I want it all! Server-side procedure with multiple input parameters returning multiple rows of multiples columns.

    In _general_, I'm OK. I understand this will work:

    CREATE TYPE my_type AS (fname varchar, lname varchar);

    CREATE FUNCTION my_function (varchar(2), BOOL) RETURNS SETOF my_type AS '
    SELECT first_name, last_name FROM people WHERE code = TRIM($1) and registered = $2;'
    LANGUAGE 'sql';

    Here the thing: In the real world, I want to work with a member database and have the DE people for events use that db. So for any particular event, there will be an "attending" column and a "cancelled" column. So any list of attendees will, for instance, SELECT first_name, last_name, from people WHERE attending IS true AND cancelled IS NOT true;

    My questions are: Is it true that LANGUAGE 'sql'; will not recognize IS and IS NOT? It complains when I try to use them. Notice that I used "=" in my example above (registered = $2 [true]). That seems to work for IS. But "<>" errors if I try to add "AND cancelled <> $3". So how do I get an "IS NOT'?

    Should I forego boolean columns and use integer columns of 1s and 0s if I want a logical field in LANGUAGE 'sql' ??

    Tnx. (using 7.3)

  2. #2
    Join Date
    Jan 2004
    Posts
    26
    I don't know much about what you are asking, but i suggest you to read the topic "handling errors of transactions in functions" if you are planning to make a server-side application. You'll find it a few lines under this topic.

  3. #3
    Join Date
    Feb 2004
    Posts
    14
    I don't see a need for duplicating the same value for a specific record. E.G., if a person is marked as 'attending', then he's obviously not cancelling.

    Otherwise, if you're planning on recording cancellations, you should normalize your tables and create a "cancellations" table, with the time, reason and an FK to the person's details.

    All in all, I find that using an integral type works fine; bool is something that would technically require somewhat special handling, as a boolean value can be represented in multiple ways, whereas a number is either 0 or not.

  4. #4
    Join Date
    Feb 2004
    Posts
    3
    [QUOTE][SIZE=1]Originally posted by Mastulator
    I don't see a need for duplicating the same value for a specific

    Perhaps "registered" would be more succinct since "attending" does seem to assume, well, "attending". But, yes, I want both. If someone registers and then cancels, I want that fact recorded in the database. Not just backing out the registration data. And it's generally three state: 1. In the member database and never registered, 2. registered, and 3. registered but cancelled.

    Otherwise, if you're planning on recording cancellations, you should normalize your tables and create a "cancellations" table, with the time, reason and an FK to the person's details.

    Hmmmmm. Well, perhaps technically some logic there and worth thinking about from a maintenance standpoint. But most of the time I will want people working from an entity table import so it seems like another table that I would almost universally link to anyway before I'd know what report records to return. While simultaneously, I might never create a "deleted" report -- just needing the information on a record-by-record basis as questions arise.

    All in all, I find that using an integral type works fine; bool is

    Well, I suppose. It just isn't so "cool" and since I'm just starting out, I figured why not use boolean for boolean?

    I didn't realize that the sort of server-side function I want returning multiple rows of multiple records from multiple input parameters is apparently quite new as a 7.3 addition? So there may be some kinks to work out.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •