Binding parameters to postgres query in Perl using names

Question

I'm working in a Perl script and I'd like to use named parameters to perform a query in a Postgres database. The DBI documentation says that's not portable:

Some drivers also allow placeholders like :name and :N (e.g., :1, :2, and so on) in addition to ?, but their use is not portable

I'd like to do that anyway. Does anyone know if the Postgres driver implement that?

Instead of performing a query like this:

 $q = $pg->prepare($query);
 $q->bind_param(1, "value");
 $q->bind_param(2, "value");
 $q->execute();

I'd like to do something like this:

 $q = $pg->prepare($query);
 $q->bind_param("parameterX", "value");
 $q->bind_param("parameterY", "value");
 $q->execute();

Cheers!

EDIT

The correct syntax is as follows (I was missing the colon):

 $q = $pg->prepare($query);
 $q->bind_param(":parameterX", "value");
 $q->bind_param(":parameterY", "value");
 $q->execute();

Impressionism

Answer 1

It is supported, but discouraged:

The final placeholder type is "named parameters" in the format ":foo". While this syntax is supported by DBD::Pg, its use is discouraged in favor of dollar-sign numbers.

The different types of placeholders cannot be mixed within a statement, but you may use different ones for each statement handle you have. This is confusing at best, so stick to one style within your program.

Backer

It works indeed. I was missing the colon before my parameter name. – Impressionism

@Andre: Numbered $n-style placeholders are recommended for PostgreSQL due to the use of : when working with PostgreSQL arrays: https://mcmap.net/q/1780159/-possible-to-use-named-placeholders-in-dbi-39-s-selectcol_arrayref-amp-co – Demobilize

I understand the recommendation, but when it comes to working with something like 30 parameters, I'd rather call them by their names and know what I am looking at right away. Thanks for the link, though. – Impressionism

Answer 2

The Postgres driver is in the module DBD::Pg (offline documentation: perldoc DBD::Pg).

Baptist

This "answer" contains as much information as statement "water is wet" – Severally