Thread: call analyze from stored procedure in C

Hi everybody!

I have to insert a lot of data (more than 1.000.000 rows) in various
tables. I use stored procedures in C to insert the data. It is necessary
to run ANALYZE after inserting a some thousand rows into a table. Can
someone tell me how to call ANALYZE
(or analyze_rel(Oid relid, VacuumStmt *vacstmt))
from a C stored procedure ? Any help would be appreciated.

Thanks

Ulli Mueckstein

--

help!

query is equivalent to "update table1 set field6 = (select table2_field2
from table2 where table2_field5 = table1.field5)"

table2 appears to have many instances of table2_field5.

my question is, how do i reformulate my SQL so that i can update table1 such
that it only gets the first occurrence of table2_field5 on table2 and ignore
all the other occurrences?  is there even a way where only 1 SQL statement
is sufficient to carry out the desired result(s)?

thanks in advance!!!

i hope i stated my question clearly, sorry if i didn't.

mel

Mel,

> query is equivalent to "update table1 set field6 = (select table2_field2
> from table2 where table2_field5 = table1.field5)"
> my question is, how do i reformulate my SQL so that i can update table1
> such that it only gets the first occurrence of table2_field5 on table2 and
> ignore all the other occurrences?  is there even a way where only 1 SQL
> statement is sufficient to carry out the desired result(s)?

There are a couple of ways.  What do you mean by "first occurance"?  First
chronologically, in primary key order, alphabetical, or something else?

UPDATE table1 SET field6 = (SELECT table2_field2
        FROM table2 WHERE table2_field5 = table1.field5
        ORDER BY table2_field9 LIMIT 1);

Or:

UPDATE table1 SET field6 = field2_min
FROM (SELECT field5, min(field2) as field2_min
        FROM table2 GROUP BY field5) t2
WHERE t2.field5 = table1.field5;

Which is better depends on the orginization of your data/tables as well as
what you mean by "first".

--
Josh Berkus
Aglio Database Solutions
San Francisco

Thanks a lot Josh!!

I wasn't thinking too hard..

but then again the 2nd option (UPDATE..SET..FROM) you gave is really
something new to me. =)

-----Original Message-----
From: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org]On Behalf Of Josh Berkus
Sent: Friday, April 04, 2003 12:45 AM
To: mel@GMANMI.TV; pgsql-novice@postgresql.org
Subject: Re: [NOVICE] ERROR: More than one tuple returned by a subselect
used as an expression.


Mel,

> query is equivalent to "update table1 set field6 = (select table2_field2
> from table2 where table2_field5 = table1.field5)"
> my question is, how do i reformulate my SQL so that i can update table1
> such that it only gets the first occurrence of table2_field5 on table2 and
> ignore all the other occurrences?  is there even a way where only 1 SQL
> statement is sufficient to carry out the desired result(s)?

There are a couple of ways.  What do you mean by "first occurance"?  First
chronologically, in primary key order, alphabetical, or something else?

UPDATE table1 SET field6 = (SELECT table2_field2
        FROM table2 WHERE table2_field5 = table1.field5
        ORDER BY table2_field9 LIMIT 1);

Or:

UPDATE table1 SET field6 = field2_min
FROM (SELECT field5, min(field2) as field2_min
        FROM table2 GROUP BY field5) t2
WHERE t2.field5 = table1.field5;

Which is better depends on the orginization of your data/tables as well as
what you mean by "first".

--
Josh Berkus
Aglio Database Solutions
San Francisco


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Mel,

> Thanks a lot Josh!!

You're welcome.

> I wasn't thinking too hard..
>
> but then again the 2nd option (UPDATE..SET..FROM) you gave is really
> something new to me. =)

Unlike *some* databases, PostgreSQL supports sub-selects just about anywhere;
in the SELECT, FROM, WHERE, and/or HAVING clauses.  I recommend buying a good
advanced SQL book to give you and idea of the possibilities, such as Joe
Celko's "SQL for Smarties, 2nd Ed.".


--
-Josh Berkus
 Aglio Database Solutions
 San Francisco

For the life of me I can't remember the names of the two rules I wrote and
now I can't figure out how to get a listing of rules.

Any help would be greatly appreciated.

Thanks.

Julie

"Juliet May" <jmay@speark.com> writes:
> For the life of me I can't remember the names of the two rules I wrote and
> now I can't figure out how to get a listing of rules.

Look in the pg_rules view.

            regards, tom lane