[SQL] "Join" on delimeter aggregate query
Hello everyone, I've set up PostgreSQL as the authentication / configuration database for my mail server (Postfix + Courier-IMAP), and though it works beautifully, I need some help on my aliases query. You see, define aliases in a database table as rows in a column in the form of "Source" and "Target". The problem is that one source address can be delivered to multiple targets (e.g. internal mailing list, or a temporary forward to another address), but postfix only processes the first record returned from an SQL query. Postfix can deliver to multiple targets, if you separate the targets with comas, like so: Source Target [EMAIL PROTECTED] [EMAIL PROTECTED],[EMAIL PROTECTED], What I would like to do, is something like the following (I know I'd need to group the query, but you get the idea): Select DISTINCT(Source), JOIN(Target, ',') FROM Aliases Is there any way this can be done with Postfix? -- Michael A Nachbaur <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] "Join" on delimeter aggregate query
Thanks very much, this helps immensely. I've worked with functions before,
but never aggregates. I guess there's some more bedtime reading for me to
look into now.
Re: sorting, this is not important to me, but I will keep the issues brought
up by Tom Lane in mind when I use this.
On Saturday 07 June 2003 02:06 pm, Eivind Kvedalen wrote:
> Hi
>
> You can create an aggregate function to solve this. A friend of mine asked
> the same question a while ago, and I created a possible example solution
> for him, which I paste here:
>
> CREATE FUNCTION concat(varchar,varchar) RETURNS varchar
> AS 'SELECT CASE
>$1 WHEN \'\' THEN $2
>ELSE $1 || \',\'|| $2
> END AS RESULT;'
> LANGUAGE SQL;
>
> /* DROP AGGREGATE concat(varchar); */
>
> CREATE AGGREGATE concat (
> BASETYPE = varchar,
> SFUNC = concat,
> STYPE = varchar,
> INITCOND = ''
> );
>
> /* Example code */
>
> DROP TABLE test;
> CREATE TABLE test (
> a varchar,
> b varchar
> );
>
> INSERT INTO test VALUES ('A', '1');
> INSERT INTO test VALUES ('A', '3');
> INSERT INTO test VALUES ('A', '2');
> INSERT INTO test VALUES ('B', 'a');
> INSERT INTO test VALUES ('C', 'b');
> INSERT INTO test VALUES ('C', 'c');
>
> SELECT a, concat(b) FROM (SELECT a,b FROM test ORDER BY a,b) T GROUP BY a;
>
> /*
>
> a | concat
> ---+-
> A | 1,2,3
> B | a
> C | b,c
>
> */
>
> The ORDER BY is included to sort the rows before they are aggregated. I'm
> not sure that this guarantees that they actually will be sorted, but maybe
> some of the postgresql hackers can confirm/deny this? I guess this isn't
> important to you, though.
>
> On Fri, 6 Jun 2003, Michael A Nachbaur wrote:
> > Hello everyone,
> >
> > I've set up PostgreSQL as the authentication / configuration database for
> > my mail server (Postfix + Courier-IMAP), and though it works beautifully,
> > I need some help on my aliases query.
> >
> > You see, define aliases in a database table as rows in a column in the
> > form of "Source" and "Target". The problem is that one source address
> > can be delivered to multiple targets (e.g. internal mailing list, or a
> > temporary forward to another address), but postfix only processes the
> > first record returned from an SQL query.
> >
> > Postfix can deliver to multiple targets, if you separate the targets with
> > comas, like so:
> >
> > Source Target
> > [EMAIL PROTECTED] [EMAIL PROTECTED],[EMAIL PROTECTED],
> >
> > What I would like to do, is something like the following (I know I'd need
> > to group the query, but you get the idea):
> >
> > Select DISTINCT(Source), JOIN(Target, ',') FROM Aliases
> >
> > Is there any way this can be done with Postfix?
>
> Eivind
--
Michael A Nachbaur <[EMAIL PROTECTED]>
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] Object-Relational table design question
Hello everyone. I'm re-designing an ISP billing / customer tracking system, and I am confused a bit about object-relational tables. PostgreSQL is the first ORDBMS I've used and, though I've been using it for about two years now, I have never taken advantage of it's inheritance functionality. (BTW, if this isn't the correct forum to post this in, please let me know.) I'm trying to represent a set of services as a series of database tables; all service "classes" will have similar data -- base price, setup price, name, product code, etc -- but some will have more specific information. For instance, a dial-up account may be restricted by the number of hours available, and then there may be an additional fee for overage. A website account may be limited to disk space, monthly bandwidth quotas, etc. I thought of defining the different services in their tables, all inherited from the base "Service" table, and then insert rows for the different services of each (for instance "Basic Webhosting", "Advanced Webhosting", etc). I'm uncertain how much mileage I'll get with this approach however. When querying for all services a customer is subscribed to, would I be able to have it return -- on a row-by-row basis -- the separate columns of each sub-table even if that row isn't available for another record? (sort of like a left outer join would be; or would I be better off just doing a plain-ol' left outer join across all my inherited service tables?) Thanks in advance. I'd appreciate any feedback you have to offer. -- Michael A Nachbaur <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Blobs with perl
On Tuesday 17 June 2003 02:00 am, cristi wrote: > I want to insert a picture in a table from an internet browser using a > script made in perl. > Has somebody a code example with this kind a problem (I need only a code > fragment)? I put album covers into a database table, primarily because I wanted them closely tied to database data without having any dependancy on a specific filesystem structure. Anyway, performance isn't much to shout about, but here's the relevant code snippet that I use to insert album images. It uses LWP::UserAgent to download the jpeg, and then plug it into my database table: my $insert_cover = $dbh->prepare(qq| UPDATE Album SET CoverLarge = ? , CoverMedium = ? , CoverSmall = ? WHERE ID = ? |); my $small_url = "$image_url?S=$image_pid&X=60&Y=60"; my $medium_url = "$image_url?S=$image_pid&X=120&Y=120"; my $large_url = "$image_url?S=$image_pid&X=178&Y=178"; return unless ($image_pid); #print "\$id = \"$id\"\n"; #print "\$small_url = \"$small_url\"\n"; #print "\$medium_url = \"$medium_url\"\n"; #print "\$large_url = \"$large_url\"\n"; my $small_image = $ua->get($small_url)->content; my $medium_image = $ua->get($medium_url)->content; my $large_image = $ua->get($large_url)->content; $insert_cover->bind_param(1, $large_image, DBI::SQL_BINARY); $insert_cover->bind_param(2, $medium_image, DBI::SQL_BINARY); $insert_cover->bind_param(3, $small_image, DBI::SQL_BINARY); $insert_cover->bind_param(4, $id); $insert_cover->execute; This comes from a throw-away script I whipped up to migrate from an older system, so the code isn't all that clean (e.g. not commented, convoluted variable names, etc) but it should get you started. -- Michael A Nachbaur <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Object-Relational table design question
On Wednesday 18 June 2003 06:20 am, Tm wrote: > On June 17, 2003 12:23 pm, Josh Berkus wrote: > > Personally, I'm not a fan of inherited tables; I think they muddy up > > the relationality of SQL without providing any additional > > We actually are doing what the original poster is in the process of > doing; we have an ISP billing system based on postgresql. I have to > agree with the above. We actually did use inheritence for a few things > (though not for account definitions), and I've always found it somewhat > of a pain. Especially when pg_dump was broken and corrupted the > database on restore... I believe this is fixed now, but I can't see the > benefit of the complication, whereas it does make the relationships > more murky as described above. Yeah, the inheritance support did seem to be a little funky, especially with the indexing problems mentioned in the documentation. > > JOIN webhosting ON service.id = webhosting.service > > This would work though it's not very scaleable. Our current system makes > all elements of a service into what we call an 'attribute'. The > attributes are defined in a table, and attached to each account type, > and turned on or off, and twiddled with various definitions such as > term/period billing, etc. This makes it relatively easy to add new > services... just add another entry in the account attributes table, > whereas with hard coded joins above, if you add more services you're > going to have to edit all of your code where joins take place. How scalable would this be? If every time you want to get details on a customer you had to do a join across several tables, multiple records of which would be combined into the same result, what kind of hit would you sustain if you had a large number of customers, attributes and/or users accessing the system? > So the billing job, for example, if you want a list of services that a > customer's account has: > > SELECT * FROM account_attribute > WHERE account_type=customer.account_type > AND bill_mode>0; > > (We go even further and do resource based accounting in yet another > relation which references the attributes... it's a bit complicated, but > I think its proving quite flexible so far, and cleaner than using > inheritance). I did something to this effect years ago on an Oracle database, but since I was just a newbie to SQL, I assumed there had to be a better way of doing this. :-) Beginners luck? -- Michael A Nachbaur <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] multi-table unique index
Instead of using the "serial" datatype, you can set it to "int4 PRIMARY KEY DEFAULT nextval(foo_type_id_seq)" and you can manually create the sequence "foo_type_id_seq". This way all the tables share the same sequence. On Monday 23 June 2003 06:58 am, Markus Bertheau wrote: > -- Suppose I have several types of foos > > create table foo_types ( > foo_type_id serial primary key, > foo_name text not null > ); > > -- And the foos itself: > > create table foo ( > foo_id serial primary key, > foo_type_id int not null references foo_types, > foo_general_data1 text > ); > > -- 1st special foo: > > create table foo_1 ( > foo_1_id serial primary key, > foo_id int not null references foo, > foo_1_data1 int, > foo_1_data2 text > ); > > -- 2nd special foo: > > create table foo_2 ( > foo_2_id serial primary key, > foo_id int not null references foo, > foo_2_data1 numeric(8,2), > foo_2_data2 timestamp > ); > > -- And I have bars that each have zero or more (special) foos. > > create table bar ( > bar_id serial primary key > ); > > create table bar_foos ( > bar_id int not null references bar, > foo_id int not null references foo > ); > > -- Now how do I prevent that I can insert a foo_1 and a foo_2 with > -- the same foo_id? Or is the design errorneous itself? > > -- > Markus Bertheau > Berlin, Berlin, Germany > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) -- Michael A Nachbaur <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] multi-table unique index
On Monday 23 June 2003 11:16 am, Markus Bertheau wrote: > В Пнд, 23.06.2003, в 19:32, Michael A Nachbaur пишет: > > Instead of using the "serial" datatype, you can set it to "int4 PRIMARY > > KEY DEFAULT nextval(foo_type_id_seq)" and you can manually create the > > sequence "foo_type_id_seq". > > > > This way all the tables share the same sequence. > > Yeah, but I want to force this behaviour. so that it cannot happen by > accident when you insert records without relying on the sequence. I believe that's what I recommended. IIRC the "serial" datatype is simply a shortcut to what I listed above. This way, if you do not explicitly specify an id for your record, it'll pull the default; which retrieves a new values from the sequence. If you want to ensure that a value is unique when a record is inserted, even if the user explicitly specifies an ID values, you can create a unique trigger on the tables, but this depends on what you want to do. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Database design - AGAIN
On Tuesday 24 June 2003 03:15 pm, Rudi Starcevic wrote: > I'll be using smaller tables with appropriate join tables and PHP. > Just for interests sake I'll be using Dia to plan the schema. > I prefer to use the UML drawing tools instead of the ER ones. Off topic, but if you're using KDE you might want to look into Umbrello. It's a UML modelling app for KDE, and is quite good, even has the beginnings of code generation (for SQL, Perl, Java, etc). -- Michael A Nachbaur <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] Temporary table performance?
Hello everyone, In the Intranet application I've been building over the past 9 months, I have developed quite a beast of a search. I've asked for help here in the past on performance tuning it, and have come to the conclusion that I've gotten it as tweaked as I can without seeking the counsel of a shamen. What I'm looking at doing is improving in-page performance. In a nutshell, how expensive is it to create a temporary table? I'd like to do something like: CREATE TEMP TABLE SearchResults AS [..]; I could then do some queries against the temporary table without having to regenerate the results every time I want to show a "Page 299 of 500" toolbar. Would I be better off just sucking this data into an in-memory data structure, or can I use a temp table as an easy-to-use alternative? -- /* Michael A. Nachbaur <[EMAIL PROTECTED]> * http://nachbaur.com/pgpkey.asc */ ...[Arthur] leapt to his feet like an author hearing the phone ring... ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] Need help creating a BEFORE DELETE trigger
Hello everyone, I'm creating two database tables that will be used to cache the results of a search. Basically, when a search is initiated, an entry will be created in the "Search" table that represents the search, and a single entry will be created in a child table "SearchResults" for every result returned. A foreign key relationship will be associated between the two. CREATE TABLE Search ( id SERIAL , accountid INTEGER REFERENCES account(id) ON DELETE CASCADE NOT NULL , sessionnum CHAR(32) UNIQUE NOT NULL , createdTIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL , sqlcodeTEXT ); CREATE TABLE SearchResults_Customer ( idSERIAL , searchid INTEGER REFERENCES search(id) ON DELETE CASCADE NOT NULL , customeridINTEGER REFERENCES customer(id) ON DELETE CASCADE -- All the results go in fields here ); Now, when any record is deleted in the SearchResults table (via an ON DELETE CASCADE, or other trigger), I'd like the entire search set to be deleted since the search is now invalid. Therefore, if a single record in the SearchResults table is deleted, I want it to instead delete the associated record in the Search table; this'll cause a CASCADE into the SearchResults table, toasting my entire result set. The problem I'm looking at is: could this cause a recursion problem, where the cascading deletion will try to cause the whole thing to cascade again? How can I set this up so I can kill an entire tree of data if any one of it's members dies? Thanks in advance. -- /* Michael A. Nachbaur <[EMAIL PROTECTED]> * http://nachbaur.com/pgpkey.asc */ "I don't know, " said the voice on the PA, "apathetic bloody planet, I've no sympathy at all. " ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Datatype conversion help
You want to use: nachbaur=# select to_char(5, '00'); to_char - 05 (1 row) By using "0", you indicate you want leading zeros. See http://www.postgresql.org/docs/view.php?version=7.3&idoc=1&file=functions-formatting.html for more information. On Tuesday 08 July 2003 01:07 pm, Yasir Malik wrote: > Suppose I have an integer between 0 and 99 and I want to covert it to > string, and pad leading zeros if neccessary. For example, > 1 => 01 > 10 => 10 > > I've tried to_char(in_val, '99'), and that returns a string that is two > charecters, but there isn't a leading zero incase I have the number 2 as > input. Any ideas? Thanks. > Yasir > > ---(end of broadcast)------- > TIP 7: don't forget to increase your free space map settings -- /* Michael A. Nachbaur <[EMAIL PROTECTED]> * http://nachbaur.com/pgpkey.asc */ "Oh no, not again." ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Datatype conversion help
http://www.postgresql.org/docs/view.php?version=7.3&idoc=1&file=functions-formatting.html#FUNCTIONS-FORMATTING-DATETIMEMOD-TABLE See the pattern modifier "FM". From the docs: "FM prefix - fill mode (suppress padding blanks and zeroes)" On Tuesday 08 July 2003 01:28 pm, Yasir Malik wrote: > Thank you so much! But my problem is that when I do > to_char(mn, '00') || '-' || to_char(dy, '00') || '-' || to_char(yr, > '') > > where mn, dy, and yr are ints, is that the output has a space after the > the dash. For example, I get > 07- 25- 1994 > > instead of what I want: > 07-25-1994 > > Thanks, > Yasir > > On Tue, 8 Jul 2003, Richard Rowell wrote: > > Date: 08 Jul 2003 15:21:33 -0500 > > From: Richard Rowell <[EMAIL PROTECTED]> > > To: Yasir Malik <[EMAIL PROTECTED]> > > Subject: Re: [SQL] Datatype conversion help > > > > On Tue, 2003-07-08 at 15:07, Yasir Malik wrote: > > > I've tried to_char(in_val, '99'), and that returns a string that is two > > > > select to_char(9,'00'); > > ---(end of broadcast)--- > TIP 8: explain analyze is your friend -- /* Michael A. Nachbaur <[EMAIL PROTECTED]> * http://nachbaur.com/pgpkey.asc */ "He expanded his chest to make it totally clear that here was the sort of man you only dared to cross if you had a team of Sherpas with you. " ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Home-brewed table syncronization
Hello everyone, While I still have plans to do some more work on RServ, it is apparent to me that I need a more immediate solution. I'm not replicating my entire dataset, but rather just some "summary" tables that are maintained by stored procedures. This means RServ is an iffy proposition at best for me right now. So, I'm looking at syncronizing 4 tables from one master database to several child databases. I'm thinking of doing the following with DBD::Multiplex: DELETE FROM TableA; INSERT INTO TableA (..) VALUES (...); on all the child databases, but I'm not sure what kind of impact this would have on my servers. My impression is that this would hammer the indexes, and might blow any memory optimization out the window. Only a few records in my dataset will change from time-to-time, but just the process of determining what is different may take more effort than simply rebuilding. What are your thoughts? These tables will probably only have a maximum of 10,000 rows in total, but I'm going to have to syncronize once per minute; as a result, I wouldn't like this to take any longer than about 10 seconds. -- /* Michael A. Nachbaur <[EMAIL PROTECTED]> * http://nachbaur.com/pgpkey.asc */ "Out," he said. People who can supply that amount of firepower don't need to supply verbs as well. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Home-brewed table syncronization
On Wednesday 09 July 2003 02:28 pm, Cliff Wells wrote: > On Wed, 2003-07-09 at 14:14, Michael A Nachbaur wrote: > > So, I'm looking at syncronizing 4 tables from one master database to > > several child databases. I'm thinking of doing the following with > > DBD::Multiplex: > > > > DELETE FROM TableA; > > INSERT INTO TableA (..) VALUES (...); > > > > > > on all the child databases, but I'm not sure what kind of impact this > > would have on my servers. My impression is that this would hammer the > > indexes, and might blow any memory optimization out the window. Only a > > few records in my dataset will change from time-to-time, but just the > > process of determining what is different may take more effort than simply > > rebuilding. > > Keep a timestamp associated with each record. Only update the records > with timestamps later than your last sync. I'm dealing with an existing database structure that, though I can change it, has a lot of impact on the rest of my infrastructure. If I can find a way of doing this without resorting to timestamps, I'd much rather do it that way. -- /* Michael A. Nachbaur <[EMAIL PROTECTED]> * http://nachbaur.com/pgpkey.asc */ "Rome wasn't burned in a day. " ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] Forcing a trigger to run
Hello all, I have several tables with triggers on them that all collectively manage a series of summary tables. In any case, there are instances where changes to a given table may cause another trigger to need to be run. What I have is an EmailAddress table, with other tables describing aliases, forwards, etc. So, if an email address is changed, the triggers on it's aliases should be run to see if they are still valid. I was thinking of putting something in the trigger for EmailAddress that would effectively say: UPDATE EmailAddressAlias SET ID=ID WHERE EmailAddressID=CurrID; but that seems like a big hack. While this would force the alias' triggers to run, it seems to me that there should be a cleaner way of doing this. Suggestions, anyone? -- /* Michael A. Nachbaur <[EMAIL PROTECTED]> * http://nachbaur.com/pgpkey.asc */ `I am so amazingly cool you could keep a side of meat in me for a month. I am so hip I have difficulty seeing over my pelvis.' ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] RFC: i18n2ascii(TEXT) stored procedure
I've created the following stored procedure to allow me to do international-insensitive text searches, e.g. a search for "Resume" would match the text "Résumé". I wanted to know: a) am I missing any characters that need to be converted? My first (and only language) is English, so I'm in the dark when that is concerned; b) is there a better and/or faster way of implementing this? I don't want searches to bog down (at least too badly) as a result of this. CREATE OR REPLACE FUNCTION i18n2ascii (TEXT) RETURNS TEXT AS ' my ($source) = @_; $source =~ tr/áàâäéèêëíìîïóòôöúùûüÁÀÂÄÉÈÊËÍÌÎÏÓÒÔÖÚÙÛÜ//; return $source; ' LANGUAGE 'plperl'; -- /* Michael A. Nachbaur <[EMAIL PROTECTED]> * http://nachbaur.com/pgpkey.asc */ "Ah, " said Arthur, "this is obviously some strange usage of the word safe that I wasn't previously aware of. " ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] RFC: i18n2ascii(TEXT) stored procedure
On Thursday 25 September 2003 05:06 pm, Manuel Sugawara wrote: > Michael A Nachbaur <[EMAIL PROTECTED]> writes: > > b) is there a better and/or faster way of implementing this? I > > don't want searches to bog down (at least too badly) as a result of > > this. > > Use to_ascii(text), [snip] D'oh! I guess thats what I get for not RTFM. :-) -- /* Michael A. Nachbaur <[EMAIL PROTECTED]> * http://nachbaur.com/pgpkey.asc */ "Oh no, not again." ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] "Permission denied" on view
I'm having a strange problem that, though I found an appropriate thread in the mailing list archive, archives.postgresql.org seems to be down so I cannot see what the resolution was. I have a database "scamp", with my own administrator user and a user "dialup". Inside the "scamp" database I've created the "dialup" schema, where a few tables reside. I've created a view called "Dialup.UserGroups" as the administrator user, and then granted select access to the dialup user on all the tables that view uses (which are all in the public schema). When I log into the database with the user dialup, if I perform the raw SQL statement it returns all the expected results, but I get the following when I try to select from the view: scamp=> select * from Dialup.UserGroup; ERROR: dialup: permission denied I have tried to create a similar, but differently named, view in the public schema to no avail. Essentially, I want to have the dialup user have access to the tables it needs, but except for one table, they should all be read-only. As such, I haven't set the ownership for the "dialup" schema to be owned by the "dialup" user. Could this perhaps be causing some problems? Any help on the matter would be appreciated (or getting the mailing list archives website up and running again ;) -- /* Michael A. Nachbaur <[EMAIL PROTECTED]> * http://nachbaur.com/pgpkey.asc */ "He expanded his chest to make it totally clear that here was the sort of man you only dared to cross if you had a team of Sherpas with you. " ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] "Permission denied" on view
On Tuesday 25 November 2003 09:09 am, Tom Lane wrote: > Michael A Nachbaur <[EMAIL PROTECTED]> writes: > > scamp=> select * from Dialup.UserGroup; > > ERROR: dialup: permission denied > > You haven't granted USAGE permission on the "dialup" schema to this > user. You need that in addition to select permission on the view itself. Thank you; apparently I didn't RTFM closely enough. /me goes sheepishly back to work -- /* Michael A. Nachbaur <[EMAIL PROTECTED]> * http://nachbaur.com/pgpkey.asc */ "`Credit?' he said. `Aaaargggh...' These two words are usually coupled together in the Old Pink Dog Bar." ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] Using chkpass() in a query
I'm trying to perform an SQL statement to extract usernames and crypt'd
passwords from a database table, and am running into difficulties with
chkpass. I can run it just fine in a straight query, but when I try to use
it on a column from an SQL statement I get an error.
scamp=# SELECT CHKPASS('foo');
chkpass
:Zbcg0W4wPBNBU
(1 row)
scamp=# SELECT CHKPASS(Password) FROM EmailAddress;
ERROR: Function chkpass(character varying) does not exist
Unable to identify a function that satisfies the given argument types
You may need to add explicit typecasts
scamp=# SELECT CHKPASS(Password::TEXT) FROM EmailAddress;
ERROR: Function chkpass(text) does not exist
Unable to identify a function that satisfies the given argument types
You may need to add explicit typecasts
scamp=# SELECT CHKPASS(Password::CSTRING) FROM EmailAddress;
ERROR: Cannot cast type character varying to cstring
Does anyone have any suggestions on how I could accomplish the above?
--
/* Michael A. Nachbaur <[EMAIL PROTECTED]>
* http://nachbaur.com/pgpkey.asc
*/
...[Arthur] leapt to his feet like an author hearing the phone ring...
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [SQL] "=" operator vs. "IS"
On June 28, 2004 03:02 pm, Stefan Weiss wrote: > I'm just curious - why is it not possible to use the "=" operator to > compare values with NULL? I suspect that the SQL standard specified > it that way, but I can't see any ambiguity in an expression like "AND > foo.bar = NULL". Is it because NULL does not "equal" any value, and the > expression should be read as "foo.bar is unknown"? Or is there something > else I'm missing? As far as I have been able to tell, it is one of those quirks about SQL that you shouldn't bother trying to understand. It just IS. -- Michael A. Nachbaur <[EMAIL PROTECTED]> http://nachbaur.com/pgpkey.asc ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
