Re: Question about PQexecParams

Hey Kenneth,

There are three benefits:

- reduces the CPU overhead in both the client and the DB server
 for converting to/from ASCII numbers

You solution based on PL/pgSQL function. I am doubt that execution
of PL/pgSQL parser (loadable module, which aren't built into the
PostgreSQL server) would be cheaper than such conversion.
Furthermore, what if I want to SELECT from table which contains
fields of different types, including arrays? How proposed PL/pgSQL
function (which returns SETOF) can be using in this case?

- decreases the amount of network traffic by more than 1/2

Agree.
 

- binary transmission of query parameters helps protect against
 SQL injection attacks

PQexecParams() was invented to protect users from SQL injections
by separating parameters from SQL command and escaping then
as well. Furthermore, there is no way to execute more than one
command with PQexecParams() (according to the doc, this provides
an extra protection from SQL injections!
 

Obviously, this is not as important for low performance systems,
with the possible exception of preventing SQL injection attacks,
but on high performance systems, minimizing the CPU overhead
due to data conversions is very useful indeed.

On the other hand, programming of such binary transmissions, IMO,
it very tedious and error prone that is not acceptable for highly reliable
systems.
 

Whether or not someone chooses to use it would need to be
evaluated on a case-by-case basis. I posted the code because
the details on how to use binary array transmission needed
close examination of the documentation as well as the database
sources. If you are using PostgreSQL 8.3 or higher, and can
use an add on library, libpqtypes provides a very nice and
very useful API for managing binary parameter transmission.
I really hope that it could be included in the core PostgreSQL
to help others avoid the need to troll through the grotty
inner-workings of the database to figure out how to do this
using just libpq.

Thanks for sharing!

--
// Dmitriy.

Random sort with distinct

Re: Random sort with distinct

Re: Random sort with distinct

Re: Random sort with distinct

"Ozer, Pam" <pozer <at> automotive.com> writes:
> Select Distinct VehicleMake, VehicleModel
> From VehicleYearMakeModelTrim
> Order by random()
> Limit 10;

> I don't want to bring back the random number I just want the sort order
> to be random. How can I sort randomly?  This query breaks because
> random() is not in the select.

Well, yeah: the implication of the ORDER BY is that a new random value
is to be computed for each row of VehicleYearMakeModelTrim.  After you
combine rows with DISTINCT it's not clear which of those values should
be used to sort a grouped row.

You need to put the DISTINCT and the ORDER BY in separate query levels,
like this:

select * from
  (Select Distinct VehicleMake, VehicleModel
   From VehicleYearMakeModelTrim) ss
Order by random()
Limit 10;

			regards, tom lane

--

-- 
Sent via pgsql-sql mailing list (pgsql-sql <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

join returns too many results...

When I join tables; I will sometimes get multiple rows back as in this 
example.

create table class(name varchar, p point, d int);
insert into class values( 'All', point(50,50), 100 );
insert into class values( 'NE70', point(70,70), 20 );
insert into class values( 'NE75', point(75,75), 20 );
insert into class values( 'NE80', point(80,80), 20 );
insert into class values( 'Centre', point(50,50), 5 );

create table list(x varchar, p point);
insert into list values('A',point(10,10));
insert into list values('B',point(75,75));
insert into list values('C',point(51,51));

select x,name,class.p<->list.p as dist, class.d as size from list left 
join class on list.p < <at>  circle(class.p,class.d);
  x |  name  |       dist       | size
---+--------+------------------+------
  A | All    | 56.5685424949238 |  100
  B | All    | 35.3553390593274 |  100
  B | NE75   |                0 |   20
  B | NE70   | 7.07106781186548 |   20
  B | NE80   | 7.07106781186548 |   20
  C | All    |  1.4142135623731 |  100
  C | Centre |  1.4142135623731 |    5

In the case where multiple rows are returned from class; I will need to 
add another condition which decides which row to return.
1) distance from centre
2) size of circle
Whatever clause I choose to add I *must* ensure that final result set 
contains only one-to-one join between tables.  Either of these two 
results is acceptable:

For option 1; result C=All or C=Centre is acceptable.
For option 2; result B=NE70 or B=NE75 or B=NE80 is acceptable.

I am lost trying to construct SQL to accomplish this...

--

-- 
Sent via pgsql-sql mailing list (pgsql-sql <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Need magic for inserting in 2 tables

  Hi,

I need to insert a lot of basically blank records into a table to be 
filled later.
Sounds silly but please bear with me.  :)

projects ( project_id, project_name, ... )
companies ( company_id, ... )
departments ( department_id, department )
staff ( staff_id  SERIAL,  company_fk, department_fk, ...   )

company_2_project ( project_fk, company_fk )
staff_2_project ( project_fk, staff_fk, project data, ... )

So with this I can store that company 99 belongs e.g. to project 3, 5 and 42
and staff_id 11, 13, 17 belongs to company 99.

staff_2_project represents the connection of staff members to a project 
and holds projectrelated infos.

Now say I have allready 100 companies out of the bigger adress pool 
connected to project 42 and I now want to add blank  staffers out of 
department  40 and 50  linked with this project.

I do step 1:

insert into staff ( company_fk, ..., department_fk )
select  company_fk, ..., department_fk
from     departments,   companies,   company_2_project  AS c2p
where  company_id      =   c2p.company_fk
     and c2p.project_fk    =   42
     and department_id  in  ( 40, 50 );

step 2 would be to link those new blank staff records to project 42 by 
inserting a record into staff_2_project for every new staff_id.

How can I find the new staff_ids while making sure I don't insert ids 
from other sessions?
Is there an elegant way in SQL ?

--

-- 
Sent via pgsql-sql mailing list (pgsql-sql <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: Need magic for inserting in 2 tables

On Sun, Oct 3, 2010 at 4:14 PM, Andreas <maps.on <at> gmx.net> wrote:
> insert into staff ( company_fk, ..., department_fk )
> select  company_fk, ..., department_fk
> from     departments,   companies,   company_2_project  AS c2p
> where  company_id      =   c2p.company_fk
>    and c2p.project_fk    =   42
>    and department_id  in  ( 40, 50 );
>
> step 2 would be to link those new blank staff records to project 42 by
> inserting a record into staff_2_project for every new staff_id.
>
> How can I find the new staff_ids while making sure I don't insert ids from
> other sessions?
> Is there an elegant way in SQL ?

Use returning?

insert into .....
yada
returning field1, field2, field3

--  To understand recursion, one must first understand recursion.

--

-- 
Sent via pgsql-sql mailing list (pgsql-sql <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: Need magic for inserting in 2 tables

  Am 04.10.2010 01:46, schrieb Scott Marlowe:
> On Sun, Oct 3, 2010 at 4:14 PM, Andreas<maps.on <at> gmx.net>  wrote:
>> insert into staff ( company_fk, ..., department_fk )
>> select  company_fk, ..., department_fk
>> from     departments,   companies,   company_2_project  AS c2p
>> where  company_id      =   c2p.company_fk
>>     and c2p.project_fk    =   42
>>     and department_id  in  ( 40, 50 );
>>
>> step 2 would be to link those new blank staff records to project 42 by
>> inserting a record into staff_2_project for every new staff_id.
>>
>> How can I find the new staff_ids while making sure I don't insert ids from
>> other sessions?
>> Is there an elegant way in SQL ?
> Use returning?
>
> insert into .....
> yada
> returning field1, field2, field3
It seams the inserts can't be chained? :(
The inner insert works when I run it separately but when I run the 
chained inserts I get an syntax error.
How can a script use what RETURNING dumps out?
I tried a bit but got nowhere.

insert into staff_2_project ( staff_fk, project_fk )
     insert into staff ( company_fk, ..., department_fk )
     [...]
     returning staff_id, 42 as project_fk;

and

insert into staff_2_project ( staff_fk, project_fk )
(
     insert into staff ( company_fk, ..., department_fk )
     [...]
     returning staff_id, 42 as project_fk
) as s;

and

insert into staff_2_project ( staff_fk, project_fk )
select staff_id, project_fk from
(
     insert into staff ( company_fk, ..., department_fk )
     [...]
     returning staff_id, 42 as project_fk
) as s;

--

-- 
Sent via pgsql-sql mailing list (pgsql-sql <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: Need magic for inserting in 2 tables

On Sun, Oct 3, 2010 at 6:47 PM, Andreas <maps.on <at> gmx.net> wrote:

> How can a script use what RETURNING dumps out?
> I tried a bit but got nowhere.

The same way it would use the output of a select, it's a record set.
So it's x rows by y columns.

-- 
To understand recursion, one must first understand recursion.

--

-- 
Sent via pgsql-sql mailing list (pgsql-sql <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql