[SQL] Trigger cant find function

[Graham Vickrage]

I seem to be having difficulty creating a trigger. I have creted the
function and tested it which seems to work fine: -

CREATE FUNCTION get_prod_cost_price (varchar, int8) RETURNS float AS '
DECLARE
cost FLOAT;
BEGIN
SELECT cost_price INTO cost FROM product WHERE code = $1;

IF FOUND THEN
UPDATE order_detail SET cost_price = cost WHERE order_detail_id=$2;
RETURN cost;
END IF;
RETURN 0;
END;
' LANGUAGE 'plpgsql';

dvd=> select get_prod_cost_price ('DVD368', 10027);
get_prod_cost_price
---
   9.81
(1 row)



Now I try and create the approprate trigger and I get the following:-

CREATE TRIGGER tg_update_order_detail AFTER insert
ON order_detail FOR EACH ROW
EXECUTE PROCEDURE get_prod_cost_price('product_id', 'order_detail_id');

ERROR:  CreateTrigger: function get_prod_cost_price() does not exist

It is clear that it does exist so why does the trigger creation code not
find it?

Thanks in advance for any pointers.

Graham

Re: [SQL] Trigger cant find function

[Tom Lane]

"Graham Vickrage" <[EMAIL PROTECTED]> writes:
> ERROR:  CreateTrigger: function get_prod_cost_price() does not exist
> It is clear that it does exist so why does the trigger creation code not
> find it?

Because the code is looking for a function of no arguments, which yours
is not.

The method for dealing with arguments passed to triggers is, um, arcane
--- I think you look in an implicitly declared array named TGARG, or
something like that.  You don't receive them as normal function
arguments, anyway.

regards, tom lane

Re: [SQL] Using a postgres table to maintain unique id?

[Thomas Swan]

At 11/13/2000 06:22 PM -0800, Michael Teter wrote:
> Can someone show me how to
create (and use) an int8
> sequence?

 From what I can tell (both from docs and doing a
describe on sequences in my database), a postgresql
sequence is an int4, not an int8, and thus you are
limited to a max of 2.1 billion values.

If you require an int8 sequence, you'll probably have
to manage your own and just use an int8 column.


I had originally started using int8 and creating custom
sequences.   However, as mentioned in a previous post, there is
an inherent performance penalty in using int8 over int4.   Tom
Lane advised me that the int8 routines are an emulated or synthesized
data type.  in the test I did on our 7.0.2 server I notice about a
25-30% decrease in performance when using complex joins on tables
containing referential keys, primary keys all in the int8 data 
type.

This might be something to think about as well.

Re: [SQL] how to continue a transaction after an error?

[Stephan Szabo]

On Tue, 14 Nov 2000, Philip Warner wrote:

> >I could
> >almost see certain recoverable internal state things being worth not doing
> >a rollback for, but not constraints.
> 
> Not true, eg, for FK constraints. The solution may be simple and the
> application needs the option to fix it. Also, eg, the triggered data
> *could* be useful in reporting the error (or fixing it in code), so an
> implied rollback is less than ideal. Finally, custom 'CHECK' constraints
> could be designed for exactly this purpose (I have done this in DBs before).

I was actually talking about commit time rollback there, not statement
time.  I could theoretically see commit time non-rollback in cases of a
presumed transient internal state thing (now, I can't think of any in
practice, but...)  

For a commit time check, I still think preceding with a set constraints
all immediate is better if you want to actually see if you're safe to
commit.

Re: [SQL] Using a postgres table to maintain unique id?

[Steve Wampler]

Thomas Swan wrote:
> 
> At 11/13/2000 06:22 PM -0800, Michael Teter wrote:
> >
> > From what I can tell (both from docs and doing a
> > describe on sequences in my database), a postgresql
> > sequence is an int4, not an int8, and thus you are
> > limited to a max of 2.1 billion values.
> >
> > If you require an int8 sequence, you'll probably have
> > to manage your own and just use an int8 column.
> >
> I had originally started using int8 and creating custom sequences.   However,
> as mentioned in a previous post, there is an inherent performance penalty in
> using int8 over int4.   Tom Lane advised me that the int8 routines are an
> emulated or synthesized data type.  in the test I did on our 7.0.2 server I
> notice about a 25-30% decrease in performance when using complex joins on
> tables containing referential keys, primary keys all in the int8 data type.
> 
> This might be something to think about as well.

Thanks.  Because of these and other comments people have made, I've gone back
to using a flat_file-with-server approach instead of adding a table to my
postgres DB.  While an int4 *might* work, it doesn't handle the "worst-case"
scenario (which is up around 15 billion values).

Thanks to everyone for your comments and suggestions!

--
Steve Wampler-  SOLIS Project, National Solar Observatory
[EMAIL PROTECTED]

[SQL] Using Array-Values in subselect

[Alvar Freude]

Hi,

i want to create a linked structure of values. Each text has an id, an
array of children ids and the value itself.

  CREATE TABLE structure
(
id int8,
children int8[],
value text
);

Now i want to select all values which are connected to a given parent #x
at once. My first idea was this:

  SELECT value 
  FROM structure
 WHERE id IN (SELECT children FROM structure WHERE id = #x);

But this causes an error because the array field just returns a string
instead of seperated values. Is there a way to make arrays return sort
of "real arrays" or something usable in a subselect in reasonable speed?


Thank you
Alvar



-- 
Alvar C.H. Freude  |  [EMAIL PROTECTED]

Demo: http://www.online-demonstration.org/  |  Mach mit!
Blast-DE: http://www.assoziations-blaster.de/   |  Blast-Dich-Fit
Blast-EN: http://www.a-blast.org/   |  Blast/english

Re: [SQL] Using Array-Values in subselect

[Roberto Mello]

Alvar Freude wrote:
> 
> Hi,
> 
> i want to create a linked structure of values. Each text has an id, an
> array of children ids and the value itself.

Looks like you want something similar to Oracle's CONNECT BY statement.
There are some solutions to that. At OpenACS we had to deal with that so
implemented something like what you described here. 
However, the methods described by Joe Celko is his book "SQL For
Smarties" on chapters 28 and 29 (I think) are better and more robust. If
you search for "trees" and related topics at the openacs.org and
arsdigita.com's web/db web bulletin boards, you'll find several hits.

-Roberto Mello

P.S: I know this doesn't directly anwser your question, but points you
to where to find the answer for yourself.
-- 
Computer ScienceUtah State University
Space Dynamics Laboratory   Web Developer
USU Free Software & GNU/Linux Club  http://fslc.usu.edu
My home page - http://www.brasileiro.net/roberto

RE: [SQL] Using Array-Values in subselect

[Edmar Wiggers]

IMHO you should use another table instead of an array.

Forget about reasonable speed when using IN sub-queries, you'll get a
sequential scan of the sub-query for every row in the master select. I've
heard the EXISTS operator provides far better performance.

In 7.1, there's a very nice solution: use the sub-query in the FROM clause
and make joins to it. Should be MUCH faster.

> But this causes an error because the array field just returns a string
> instead of seperated values. Is there a way to make arrays return sort
> of "real arrays" or something usable in a subselect in reasonable speed?

Re: [SQL] Using Array-Values in subselect

[Alvar Freude]

Roberto Mello schrieb:
> Looks like you want something similar to Oracle's CONNECT BY statement.
> There are some solutions to that. At OpenACS we had to deal with that so
> implemented something like what you described here.
> However, the methods described by Joe Celko is his book "SQL For
> Smarties" on chapters 28 and 29 (I think) are better and more robust. If
> you search for "trees" and related topics at the openacs.org and
> arsdigita.com's web/db web bulletin boards, you'll find several hits.

Thanks, the solutions discussed on these sites suggest the use of lookup
tables with parent and child for each record. I have thought about the
same, but thought the arrays in Postgres could provide a more elegant
way of building a tree.

I want to migrate from MySQL to Postgres mainly because the array and
subselect features. And finally the article at
http://www.phpbuilder.com/columns/tim20001112.php3 convinced me it could
run on my small machine. But it appears to me that the arrays always are
handled like strings and are not really useful for anything advanced. I
can't really believe it, please prove me wrong! I have set great hope in
Postgres. :)


Alvar

-- 
Alvar C.H. Freude  |  [EMAIL PROTECTED]

Demo: http://www.online-demonstration.org/  |  Mach mit!
Blast-DE: http://www.assoziations-blaster.de/   |  Blast-Dich-Fit
Blast-EN: http://www.a-blast.org/   |  Blast/english

Re: [SQL] Using Array-Values in subselect

[Stephan Szabo]

If you look in contrib of the source, there is a set of
array operators(functions) including element in set.
That'll probably do what you want (you don't do an in
actually, it'll be like   )

On Tue, 14 Nov 2000, Alvar Freude wrote:

> Roberto Mello schrieb:
> > Looks like you want something similar to Oracle's CONNECT BY statement.
> > There are some solutions to that. At OpenACS we had to deal with that so
> > implemented something like what you described here.
> > However, the methods described by Joe Celko is his book "SQL For
> > Smarties" on chapters 28 and 29 (I think) are better and more robust. If
> > you search for "trees" and related topics at the openacs.org and
> > arsdigita.com's web/db web bulletin boards, you'll find several hits.
> 
> Thanks, the solutions discussed on these sites suggest the use of lookup
> tables with parent and child for each record. I have thought about the
> same, but thought the arrays in Postgres could provide a more elegant
> way of building a tree.
> 
> I want to migrate from MySQL to Postgres mainly because the array and
> subselect features. And finally the article at
> http://www.phpbuilder.com/columns/tim20001112.php3 convinced me it could
> run on my small machine. But it appears to me that the arrays always are
> handled like strings and are not really useful for anything advanced. I
> can't really believe it, please prove me wrong! I have set great hope in
> Postgres. :)

Re: [SQL] Using Array-Values in subselect

[Tom Lane]

Stephan Szabo <[EMAIL PROTECTED]> writes:
> If you look in contrib of the source, there is a set of
> array operators(functions) including element in set.
> That'll probably do what you want (you don't do an in
> actually, it'll be like   )

The array stuff is pretty simplistic at the moment, and could be taken
a lot further if there were someone who wanted to work on it.
(hint hint)

regards, tom lane

[SQL] FTI, paged, ranked searching and efficiency.

[Paul]

Hello,

This is going to be a bit long, I hope some of you will take the
trouble to read it :)

I am building a search engine for a section of a (PHP based) website.
I wish the user to be able to a number of words in the search, and the
search results to be ranked by the number of times words occur (both
different words and the same word occuring multiple times are good).

My (simplified) table structure is this:
==
 Table "entry_fti"
 Attribute |Type | Modifier
---+-+--
 string| varchar(25) |
 id| oid |
Index: entry_fti_string_idx

   Table "entry"
   Attribute   | Type  |Modifier
---+---+--
-
 entry_id  |integer| not null default 
nextval('entry_id_seq'::text)
 entry_name|text   |
 entry_description_html|text   |
 entry_image_id|integer| not null default 0
 entry_tn_image_id |integer| not null default 0
 entry_live|boolean| not null default 't'
Index: entry_pkey

   Table "image"
 Attribute  |Type |Modifier
+-+
 image_id   | integer | not null default nextval('image_id_seq'::text)
 image_name | varchar(32) |
 height | integer | not null
 width  | integer | not null
Indices:  image_pkey
==

And my (simplified) query looks like this:
==
SELECT   COUNT(entry_fti.id) AS rating,
 entry.entry_name AS name,
 entry.entry_id AS id,
 entry.entry_description_html AS description_html,
 image.image_name AS thumb1_name,
 image.height AS thumb1_height,
 image.width AS thumb1_width
FROM entry, entry_fti, image
WHEREentry_fti.id=entry.oid
  ANDentry.entrytn_image_id=image.image_id
  ANDentry.entry_live = 't'::bool
  AND(
  entry_fti.string ~'^word1'
  OR
  entry_fti.string ~'^word2'
  OR
   .
   .
  OR
  entry_fti.string ~'^wordn'
 ) 
GROUP BY entry.entry_id,
 entry.entry_name,
 entry.entry_description_html,
 image.image_name,
 image.height,
 image.width
ORDER BY rating DESC 
==

Now this all works, which is good. My problem now is that I want to 
limit the number of results shown on a page to 20 and show the number
of pages of extra results, much like you'd see on any search engine site.
Naturally I immediatly thought of the LIMIT and OFFSET clauses, but then:
a) I'd need to do an extra query, to find out the total number of results
   to show the number of pages on the webpage.
b) I have no idea how to write that query. It'd be a COUNT of 'rating'
   in the above, which would be a COUNT(COUNT(entry_fti.id)) which
   would probably require some hideous (and not legal?) double GROUP
   BY construct. Ouch.

So basically, LIMIT/OFFSET looks like a no go. This leaves me with just
doing the above query, and using PHP to jump to a particular row in the
results depending on what page you are on and pg_numrows() to 
calculate the number of pages.

Would that be particularly inefficient? 
Should I be looking harder for a LIMIT/OFFSET based solution?

Perhaps I'd be better off splitting it into two queries, one to just
get the entry_id list in order, then another query to pull out the
rest of the information for the 20 of those entry_ids that are on the results 
page I wish to show?
That would stop Postgres from gathering so much information that I am just 
going to throw away anyway without looking at.

Any ideas? Have I missed something obvious that will help me? Or better yet, 
can someone who has done this sort of thing before tell me whether I am on the 
right track?

Paul

Re: [SQL] Using Array-Values in subselect

[Alvar Freude]

Tom Lane schrieb:
> 
> The array stuff is pretty simplistic at the moment, and could be taken
> a lot further if there were someone who wanted to work on it.
> (hint hint)

:)

If i had time, this would be interesting, but I think my C experiences
are not very good and some years old (I like much more 68k Assembler --
or Perl) and i have to finish my Diploma work in january:
Art&Media-Design, no informatics ... ;)


Ciao
  Alvar

-- 
Alvar C.H. Freude  |  [EMAIL PROTECTED]

Demo: http://www.online-demonstration.org/  |  Mach mit!
Blast-DE: http://www.assoziations-blaster.de/   |  Blast-Dich-Fit
Blast-EN: http://www.a-blast.org/   |  Blast/english