Re: [SQL] cannot create function that uses variable table name
As per Matthew Nuzum's post My query is very similar... I need sql functions to update the database. If I specify the filename etc they work. BUT that leads to 6 functions which are exactly the same apart from the file they update. 1) why can't I use a variable name and 2) could someone please point me towards some examples of EXECUTE if thats the only way to do it? my example is: CREATE FUNCTION retire(varchar, varchar, int4) returns int4 AS' BEGIN UPDATE $1 SET "Retired" = 'true' WHERE $2 = $3; SELECT (whatever to return the int4); END;' Language 'plpgsql'; which has: parse error at or near "true" ($1 is the filename, $2 is the fieldname, $3 is the fieldvalue and "Retired" is a boolean field in each of the files) Thanks Beth ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] cannot create function that uses variable table name
Thanks for your reply David...
1)The "" round Retired are to label the column/field 'Retired' rather
than 'retired' (someone else created the database with Upper case
titles!)
2) Your code is correct.. cept that single quotes have to be escaped(?!)
so the following will do the trick when updating text fields...
CREATE FUNCTION temp(text,text,int4) RETURNS integer AS '
DECLARE
update_table ALIAS FOR $1;
update_field ALIAS FOR $2;
update_id ALIAS FOR $3;
BEGIN
EXECUTE ''UPDATE ''|| quote_ident(update_table) || '' SET "Retired" =
true WHERE '' || quote_ident(update_field) || '' = '' ||
quote_literal(update_id);
RETURN update_id;
END;
' language 'plpgsql';
which creates...
and: select temp('TableName', 'TableID', 20);
returns 20.
On Fri, 2003-01-24 at 13:13, David Durst wrote:
> > I need sql functions to update the database. If I specify the filename
> > etc they work. BUT that leads to 6 functions which are exactly the same
> > apart from the file they update.
> >
> > 1) why can't I use a variable name and
> > 2) could someone please point me towards some examples of EXECUTE if
> > thats the only way to do it?
> >
> >
> > my example is:
> >
>
> This should work
>
> CREATE FUNCTION retire(varchar, varchar, int4) returns int4 AS'
> DECLARE
>varone ALIAS FOR $1;
>vartwo ALIAS FOR $2;
>varthr ALIAS FOR $3;
> BEGIN
> UPDATE varone SET "Retired" = 'true' WHERE vartwo = varthr;
> SELECT (whatever to return the int4);
> END;'
> Language 'plpgsql';
>
>
> And I am not sure of the purpose for the "" around Retired
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] trying to pattern match to a value contained in a column
Hi- I can't figure out how to do this I examine a table where I think that one attribute is an abbreviation of another attribute. So-If I had a table where I had LONG_NAME and ABBR as attributes. I want something like SELECT whatever FROM my_table WHERE long_name LIKE '%[the value of ABBR in that row]%'; Of course this doesn't work... Any thoughts? Thanks- Beth
Re: [SQL] trying to pattern match to a value contained in a column
Hi Francis-
Thank you for your rapid and excellent response.
This makes perfect sense...unfortunately it isn't working...
I hope this isn't because I am using 6.3 (yes...I know it is very very
old but this is currently where the data is!)
here is the query:
select * from av34s1 where chromat ~~ ('%' || sample || '%');
ERROR: parser: syntax error at or near "||"
I have also tried using LIKE
samething..
NOW..
select * from av34s1 where chromat~sample;
ERROR: There is no operator '~' for types 'bpchar' and 'bpchar'
You will either have to retype this query using an explicit
cast,
or you will have to define the operator using CREATE OPERATOR
Indeed...
Table= av34s1
+--+--+---+
| Field | Type|
Length|
+--+--+---+
| contig | char()
|10 |
| contig_pos | char()
|10 |
| read_pos | char()
|10 |
| chromat | char()
|30 |
| sample | char()
|30 |
| allele1 | char()
|10 |
| allele2 | char()
|10 |
| ref_pos | char()
|10 |
| ref_sample | char()
|10 |
| tag | char()
|10 |
| source | char()
|10 |
+--+--+---+
Thanks for your response...
Beth
Francis Solomon wrote:
> Hi Beth,
>
> Try something like this ...
>
> Here's a simple table schema:
>
> CREATE TABLE abbrev (
> abbr varchar(10),
> long_name varchar(50),
> primary key(abbr)
> );
>
> Throw in some random data:
>
> INSERT INTO abbrev VALUES ('fs', 'fsolomon');
> INSERT INTO abbrev VALUES ('bg', 'bgatewood');
> INSERT INTO abbrev VALUES ('junk', 'nomatch');
>
> Query the table:
>
> SELECT * FROM abbrev WHERE long_name~abbr;
>
> ... which yields these results:
>
> abbr | long_name
> --+---
> fs | fsolomon
> bg | bgatewood
>
> Note that ~ does a case-sensitive regex match. If you really want a
> 'like' match, you could do this instead:
>
> SELECT * FROM abbrev where long_name~~('%' || abbr || '%');
>
> ... where '||' is the string-concatenation operator.
>
> Hope this helps
>
> Francis Solomon
>
> > -Original Message-
> > From: [EMAIL PROTECTED]
> > [mailto:[EMAIL PROTECTED]]On Behalf Of Beth Gatewood
> > Sent: 07 December 2000 21:06
> > To: [EMAIL PROTECTED]
> > Subject: [SQL] trying to pattern match to a value contained
> > in a column
> >
> >
> > Hi-
> >
> > I can't figure out how to do this
> >
> > I examine a table where I think that one attribute is an
> > abbreviation of
> > another attribute.
> >
> > So-If I had a table where I had LONG_NAME and ABBR as attributes.
> >
> > I want something like
> >
> > SELECT whatever FROM my_table WHERE long_name LIKE '%[the
> > value of ABBR
> > in that row]%';
> >
> >
> > Of course this doesn't work...
> >
> > Any thoughts?
> >
> > Thanks-
> > Beth
> >
> >
> >
Re: [SQL] make a unique index for foreign keys?
Chris/ Josh- OK-response to Chris below. Synopsis heresimply by creating a foreign key will not create an index. On large tables I SHOULD put a non-unique index on the foreign key (right?) Hmm...nope. Creating a foreign key doesn't create an index. However, creating a primary key does - sure you're not getting mixed up there? You are absolutely right! I am confused I did the actual test //create the tables beth=> create table parent(pk INTEGER primary key); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'parent_pkey' for table 'parent' CREATE beth=> create table child (fk INTEGER references parent(pk) ); NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) CREATE // now get the index oid for the parent table (there should only be 1 index for the primary key) beth=> select pg_index.* from pg_index, pg_class where pg_class.oid = pg_index. indrelid and pg_class.relname='parent'; indexrelid | indrelid | indproc | indkey | indclass | indisclustered | indisunique | indisprimary | indreference | +--+-++--+-- --+-+--+--+- 26271 |26269 | - | 1 | 1978 | f | t | t|0 | (1 row) // get the index for the parent table--and it is the index for the primary key beth=> select pg_get_indexdef(26271); pg_get_indexdef -- -- CREATE UNIQUE INDEX parent_pkey ON parent USING btree (pk) (1 row) //now try that same thing for the child table and there are NO indices (also confirmed by \di) beth=> select pg_index.* from pg_index, pg_class where pg_class.oid = pg_index. indrelid and pg_class.relname='child'; indexrelid | indrelid | indproc | indkey | indclass | indisclustered | indisunique | indisprimary | indreference | +--+-++--+-- --+-+--+--+- (0 rows) Postgres doesn't create indices for you, unless as part of a primary key or unique constraint. OK-I've got it now!!! Thank you so much Beth ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] writing a function to mimic mysql last_insert_id
Hi all- I am wondering if I can get some input on the logic that I would need to code a function that would mimic mysql's last_insert_id().. Here is what last_insert_id() does in mysql: Returns the last automatically generated value that was inserted into an AUTO_INCREMENT column. mysql> SELECT LAST_INSERT_ID(); -> 195 The last ID that was generated is maintained in the server on a per-connection basis. It will not be changed by another client. It will not even be changed if you update another AUTO_INCREMENT column with a non-magic value (that is, a value that is not NULL and not 0). If you insert many rows at the same time with an insert statement, LAST_INSERT_ID() returns the value for the first inserted row. The reason for this is so that you it makes it possible to easily reproduce the same INSERT statement against some other server. If expr is given as an argument to LAST_INSERT_ID(), then the value of the argument is returned by the function, and is set as the next value to be returned by LAST_INSERT_ID(). This can be used to simulate sequences: First create the table: mysql> CREATE TABLE sequence (id INT NOT NULL); mysql> INSERT INTO sequence VALUES (0); Then the table can be used to generate sequence numbers like this: mysql> UPDATE sequence SET id=LAST_INSERT_ID(id+1); You can generate sequences without calling LAST_INSERT_ID(), but the utility of using the function this way is that the ID value is maintained in the server as the last automatically generated value (multi-user safe). You can retrieve the new ID as you would read any normal AUTO_INCREMENT value in MySQL. For example, LAST_INSERT_ID() (without an argument) will return the new ID. The C API function mysql_insert_id() can also be used to get the value. Note that as mysql_insert_id() is only updated after INSERT and UPDATE statements, so you can't use the C API function to retrieve the value for LAST_INSERT_ID(expr) after executing other SQL statements like SELECT or SET. FROM: http://www.mysql.com/doc/en/Miscellaneous_functions.html Thanks for any pointers on how to tackle this problem (and if it is possible ;-) I am thinking that I need to store in a table for each transaction whatever autoincremented values were created (which they are for all my primary keys) -Beth ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] writing a function to mimic mysql last_insert_id
Josh and Ross-
Thanks...I am well aware of currval and sequence use, and for me too this
would be the easiest solution ;-)
However, we are in the midst of porting a mysql database to pgsql. The perl
application that is tied to this mysql database heavily utilizes
last_insert_id. In an effort to save the perl developers the pain of having
to go in and modify a bunch of their SQL to call out to seq.currval, I
thought I would attempt to mimic this function.
Perhaps there is another way to approach this than writing this type of
function?
Thanks!
Beth
> -Original Message-
> From: Josh Berkus [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, September 11, 2002 10:31 AM
> To: Beth Gatewood; [EMAIL PROTECTED]
> Subject: Re: [SQL] writing a function to mimic mysql last_insert_id
>
>
>
> Beth,
>
> > I am wondering if I can get some input on the logic that I
> would need to
> > code a function that would mimic mysql's last_insert_id()..
>
> Um, what about CURRVAL('sequence-name')?
>
> --
> -Josh Berkus
> Aglio Database Solutions
> San Francisco
>
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] writing a function to mimic mysql last_insert_id
well, I know that I will have only a single sequence that will generate the primary key per table. So basically, this type of function, for me needs only to return the value of the primary key. I believe I mentioned in one of my posts the motivation behind not wanting to use currval()...which was to trying to avoid having the developers make a lot of sql revisions to their application. Thanks-Beth > -Original Message- > From: Christopher Kings-Lynne [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, September 11, 2002 7:05 PM > To: Beth Gatewood; [EMAIL PROTECTED] > Subject: RE: [SQL] writing a function to mimic mysql last_insert_id > > > But in Postgres, you can have as many "autoincrement" columns > in a table as > you want - how will you handle that? > > And what's wrong with select currval()? > > Chris > > > I am wondering if I can get some input on the logic that I > would need to > > code a function that would mimic mysql's last_insert_id().. > > > > Here is what last_insert_id() does in mysql: > > Returns the last automatically generated value that was > inserted into an > > AUTO_INCREMENT column. > > mysql> SELECT LAST_INSERT_ID(); > > -> 195 > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] indexing on char vs varchar
Thanks for the excellent description, Josh. And the reminder of the performance list (it's existence somehow slipped from my mind). -Beth > -Original Message- > From: Josh Berkus [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, October 02, 2002 10:56 AM > To: Beth Gatewood; 'Bruce Momjian' > Cc: [EMAIL PROTECTED] > Subject: Re: [SQL] indexing on char vs varchar > > > Beth, > > Oh, and you should take this sort of question to the new > performance list: > [EMAIL PROTECTED] > > -- > -Josh Berkus > Aglio Database Solutions > San Francisco > ---(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] indexing on char vs varchar
Hi- This is more just trying to understand what is going on under the hood of pgsql. I have read through the archives that there is no difference between index on char, varchar or text. I am wondering why? I understand all the arguments about saving space but I am specifically asking about index performance and wondering about the underworkings of indices based on char and varchar. Othe RDBMS have clear leanings that indexing on chars are a better way to go. In MySQL this is due to a static table characteristics (http://www.mysql.com/doc/en/Static_format.html) and speed for an index look-up (row number X row length). and the ease to read a constant number of records with each disk. In the case of Oracle, the suggestion for char is based on if using varchar2 that takes 5 char and then there is a subsequent update to this field to now take 20 char, but now the record can not grow physically...so they essentially mark the old one as deleted and create a new record at the top (in an entirely new block) but the problem is that the index points to the deleted block...so the index has to query the old block and then the new(info from: http://groups.google.com/groups?q=oracle+char+vs+varchar+index&hl=en&lr=&ie= UTF-8&oe=UTF-8&selm=3a791aa3%40news.iprimus.com.au&rnum=1) Thanks for explaining this to me -Beth ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] indexing on char vs varchar
SorryI don't understand. The length is at the front of what? -Beth > -Original Message- > From: Bruce Momjian [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, October 02, 2002 10:06 AM > To: Beth Gatewood > Cc: [EMAIL PROTECTED] > Subject: Re: [SQL] indexing on char vs varchar > > > > We store all the text/char/varchar types with the length at > the front so > we don't have such optimizations. We do have "char", in quotes, which > is a single character, but that's about it. > > -- > - > > Beth Gatewood wrote: > > Hi- > > > > This is more just trying to understand what is going on > under the hood of > > pgsql. I have read through the archives that there is no > difference between > > index on char, varchar or text. I am wondering why? I > understand all the > > arguments about saving space but I am specifically asking > about index > > performance and wondering about the underworkings of > indices based on char > > and varchar. > > > > Othe RDBMS have clear leanings that indexing on chars are a > better way to > > go. > > > > In MySQL this is due to a static table characteristics > > (http://www.mysql.com/doc/en/Static_format.html) and speed > for an index > > look-up (row number X row length). and the ease to read a > constant number of > > records with each disk. > > > > In the case of Oracle, the suggestion for char is based on if using > > varchar2 that takes 5 char and then there is a subsequent > update to this > > field to now take 20 char, but now the record can not grow > physically...so > > they essentially mark the old one as deleted and create a > new record at the > > top (in an entirely new block) but the problem is that the > index points to > > the deleted block...so the index has to query the old block > and then the > > new(info from: > > > http://groups.google.com/groups?q=oracle+char+vs+varchar+index &hl=en&lr=&ie= > UTF-8&oe=UTF-8&selm=3a791aa3%40news.iprimus.com.au&rnum=1) > > Thanks for explaining this to me > -Beth > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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
