Dynamic SQL queries for Postgres and PHP

Question

What is the difference in writing something like this:

SET SCHEMA 'schema';
        SELECT  table2.field1,
                table2.field2,
                table2.field3,
                table2.field4,
                table2.field5,
                table2.field6
            FROM table2
            WHERE table2.field1 LIKE '%{$search}%' and table2.field2 LIKE '%{$search2}%' and table2.field3 LIKE '%{$search3}%' and table2.field4 LIKE '%{$search4}%' and (table2.field5 ~* '{$search5}' {$radioa} table2.field6 ~* '{$search6}') 
            ORDER BY table1.field1 DESC, table1.field2 LIMIT {$radiob};

And something in plpgsql? What I mean is will I benefit in speed or efficiency anymore if I translated the above to plpgsql? Should I stick to the bland, dynamic statement in PHP, or go bananas with plpgsql?

Pavel Stehule · Accepted Answer · 2016-03-10 20:16:39Z

1

The execution time of queries are same in both cases. Is not important if you run the query from client of from PLpgSQL. The difference is in result processing. When you process result in server side (from PLpgSQL), then the network and data type conversions overhead is zero. The data processing on client side is much more expensive - for frequently called queries or large result is processing on client side significantly slower.

Other benefit of stored procedure (server side code) is accessibility from heterogeneous environment (you can share this code well). PHP code you can call simply only from PHP or via HTTP protocol. But this advantage is not significant in monolithic environments (all is Java or all is PHP).

answered Mar 10, 2016 at 20:16

Pavel Stehule

46.7k6 gold badges103 silver badges102 bronze badges

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

1 Comment

John Over a year ago

Oooo the universal code part is a very good point. Thank you.

Collectives™ on Stack Overflow

Dynamic SQL queries for Postgres and PHP

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related