[GENERAL] Re: Syntax for wildcard selection

[Scott Holmes]

The field is, indeed, a char(17) field.  This particular database is actually 
a copy of the same schema we use in our Informix applications.  The 
definitions for that system were almost completely correct for creating the 
PostgreSQL version, thus many fields are defined as char(x).  I shall try 
redefining those fields that are of variable length as varchar() and see what 
happens.

Thanks...

> The only thing I can think of is that you are using char() fields and the
> like is getting confused by the trailing spaces. Certainly putting wildcards
> anywhere in the string works fine.
> 
> What is the data type of your column? text and varchar() wouldn't suffer
> from the above problem.
> -- 
> Martijn van Oosterhout <[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

[GENERAL] I need a jump start

[Scott Holmes]

I need to write a C program that will require libpq and ecpg.  I'm hoping 
someone can point me to an example.  I'm not a C programmer, so I need a crash 
course or jump start.  The original program has existed as an Informix 4GL 
program for many years.  We have lately been converting our application to 
PostgreSQL.  All the user interfaces have been written with a rather obscure 
set of tools to be run on Windows clients.  This particular program needs to 
be run with cron on the server.  Essentially, it will scan a relatively short 
list of records, compare values with records or tuples from a relatively large 
table then insert into and or update other tables.  Interestingly enough, the 
first table's name is trigger.  As most of the actions required are time 
delayed (days or weeks), triggers are not a viable option for this.

Does anyone have a suggestion for an existing program or code I can study to 
give myself a leg up on this?

Thanks, Scott


---(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

[GENERAL] Compiling on SCO

[Scott Holmes]

I'm attempting to compile 7.0.2 on a SCO box.  The compile fails attempting to 
build ecpg, complaining that nocachegetattr is undefined, first referenced in 
pgc.o

I'm unable to find any reference to this.  I used the configure options 
described in the FAQ_SCO file along with --with-odbc but to no avail.

Does anyone have any suggestions as to what I might look for.

Thanks,  Scott

[GENERAL] I wish to thank...

[Scott Holmes]

...the list for inadvertently solving my problem about subscripting.  I asked 
for a suggestion about rephrasing a query with "...peopcode[1,2] = my_var".  
The obvious solution was "...substr(peopcode,1,2)"

Thanks for the unrelated question about updates.  

Scott

[GENERAL] Getting closer with functions, but...

[Scott Holmes]

This function works but is not selective enough.  The tg_argv[] are pointers 
to the arguments passed.  And they do work correctly.

CREATE FUNCTION del_stxnoted () RETURNS opaque AS '
declare
  fname text;
  rkey text;
  BEGIN
fname := tg_argv[0];
rkey := ''old.'' || tg_argv[1];
delete from stxnoted where record_key = old.wher;
return old;
  END;'
LANGUAGE 'plpgsql';

What I need can be accomplished with a rather massive if/then series:

if fname = "location" then
  delete from stxnoted where filename = "location" and record_key = old.wher;
end if

if fname = "events" then
  delete from stxnoted where filename = "events" and record_key = old.evntnmbr;
end if

... ( maybe 100 different tables )...

It seems the sql statement needs old.field or new.field to provide a value.  
Unfortunately, my first value, fname, is not a field value but a table name 
and my second value may have one of several field names.

Is there a syntax that will allow me to build an sql statement for use in 
theis funcation (ie delete from stxnoted where filename = fname and record_key 
= rkey).

Thanks, Scott

Re: [GENERAL] Triggers with arguments

[Scott Holmes]

What I'm after are triggers on delete fro several tables calling the same 
procedure.  I am not at liberty to change the schema of the database as I need 
to accomodate what is actually an ancient system - that goes back before the 
days of blobs and large text fields.  In my example, deleting a record from 
the "location" table, the trigger needs to delete associated rows from the 
notes table.  The notes table is indexed by fields for 1) a table name (in 
this case "location", and 2) a record key (the value of the primary field in 
the location table).

In postgresql, the trigger execute a procedure that must be defined with no 
arguments and returning opaque.  What I don't know how to do is write a 
procedure that knows the values for the notes table index key (table name and 
primary key value).  Examples in the manual indicate that arguments may indeed 
be passed, and it refers to such procedures as general trigger functions.  I, 
however, have not been able to figureout how to write such functions.

Thanks, Scott