Combine the stored procedure and SQL query into one SQL statement

Question

I am a beginner in SQL and I don't know how to solve this question. How to combine the following SQL query and the stored procedure into a single SQL query. This is a PostgreSQL query and function. Any help is appreciated.

The following query calls a stored procedure/function for each value of the query:

 SELECT t.trj_id, T2.trj_id, Similar_trj_woffset(t.trj_id, T2.trj_id) 
 FROM   transitions T1, 
        transitions T2 
 WHERE  T1.w_s = T2.w_s 
 AND T1.w_e = T2.w_e 
 AND T1.trans_id <> T2.trans_id

The stored procedure/function is:

create or replace function similar_trj_woffset(
  IN t1 as integer,
  IN t2 as integer,
  OUT score as integer,
  OUT offset as integer
) AS $$
declare 
off_set integer :=0;
score  integer := 0;
cou    integer;
time1  integer;
time2  integer;
dist   integer;
begin
for off_set in 0..10 LOOP
       time1 := 0;
       time2 := 0 + off_set;
       cou := 0;
       while time2 < 100 Loop 
          select vec_length(P.x-P2.x,P.y-P2.y,P.z-P2.z) into dist
          from AtomPositions P, AtomPositions P2
          where P.trj_id  = t1
            and   P2.trj_id = t2
            and   P.t = time1
            and   P2.t = time2;
        if dist < cuto`enter code here`ff then
            cou : = cou + 1;
        time1 := time1 + 1;
        time2 := time2 + 1;
    end loop
    if cou > score then
       score := cou ;
       offset := off_set;
    end if;
    off_set := off_set + 1;
end loop;

end $$ language plpgsql;

Can some one tell me how to merge the query and the stored procedure into one single SQL query.

I want to merge it for query optimization.

navin
– navin

2013-03-10 02:23:20 +00:00
Commented Mar 10, 2013 at 2:23 — navin
– navin, Commented Mar 10, 2013 at 2:23

Erwin Brandstetter · Accepted Answer · 2013-03-11 07:38:23Z

1

To call this function an split the returned record into individual columns:

SELECT t.trj_id, t2.trj_id, (similar_trj_woffset(t.trj_id, t2.trj_id)).*
FROM   transitions t1
JOIN   transitions t2 USING (w_s, w_e)
WHERE  t1.trans_id <> t2.trans_id

Note the parenthesis around the function call.
I also rewrote the query to use proper ANSI JOIN syntax with a simplified equijoin condition (USING) and removed the spurious upper-casing.

answered Mar 11, 2013 at 7:38

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Just Aguy · Accepted Answer · 2013-03-10 01:58:47Z

0

You shouldn't merge them is your basic answer. One purpose of the code being in a function is so it can be called from multiple queries and be maintained in one place. Merging the two doesn't make sense when the query is already calling the function on each iteration.

What are you expecting to gain from doing this because simply moving the code isn't going to do anything positive?

Actually, if any other queries use it, they will break if you remove it.

edited Mar 10, 2013 at 1:58

answered Mar 10, 2013 at 1:53

Just Aguy

3271 silver badge7 bronze badges

1 Comment

navin Over a year ago

Only this query uses this function and I want to merge them for the sake of query optimization.

Collectives™ on Stack Overflow

Combine the stored procedure and SQL query into one SQL statement

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related