Postgresql 9.5 and Shell scripts/variables vs. C programming/defining a value to be used

On 11/21/2016 06:56 AM, Poul Kristensen wrote:

Hi!

Hopefully this is this list.

A shell variabel is defined like this

var1= value

used like ${var1}.

How is the equal defened in the Postgresql C programming?

You probably need to be more specific about what aspect of using C in
Postgres you are talking about. Assuming 'ECPG - Embedded SQL in C' for
now:

https://www.postgresql.org/docs/9.5/static/ecpg-variables.html

Thanks.

Poul

BTW: I have not received my ordered book yet!

There is one online:):

https://www.postgresql.org/docs/9.5/static/index.html

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Mon, Nov 21, 2016 at 8:56 AM, Poul Kristensen <bcc5226@gmail.com> wrote:

Hi!

Hopefully this is this list.

A shell variabel is defined like this

var1= value

used like ${var1}.

How is the equal defened in the Postgresql C programming?

Thanks.

Poul

BTW: I have not received my ordered book yet!

Assuming I understand what you want, you want to write a program in the C
language which interfaces with PostgreSQL. There are two methods to do
this. One is to use libpq and do everything in "regular C" with calls to
the libpq routines as documented here:
https://www.postgresql.org/docs/9.5/static/libpq.html . This is, IMO, the
more powerful interface, but it takes a lot more detailed programming on
your part.
The second way is with "embedded SQL" which may be a bit easier to write,
read, and understand. That is documented here:
https://www.postgresql.org/docs/9.5/static/ecpg.html .​

--
Heisenberg may have been here.

Unicode: http://xkcd.com/1726/

Maranatha! <><
John McKown

On Mon, Nov 21, 2016 at 11:22 AM, Poul Kristensen <bcc5226@gmail.com> wrote:

Thank you for fast repons!

The $1 substitution below. I assume that it refers to "joe's place". But
it is not very clear to me, how "joe's place" will appear instead of $1
when running. Where is it possiible to read more about this? There just
is'nt much about substitution in C online. Any recommended books to buy?

/* Here is our out-of-line parameter value */
paramValues[0] = "joe's place";

res = PQexecParams(conn,
"SELECT * FROM test1 WHERE t = $1",
1, /* one param */
NULL, /* let the backend deduce param type */
paramValues,
NULL, /* don't need param lengths since text */
NULL, /* default to all text params */
1); /* ask for binary results */
}

/Poul

​It is described better here:
https://www.postgresql.org/docs/9.6/static/libpq-exec.html
than I can do. But I just noticed a mistake in your code, or maybe just
something left out. I would say:

char *value1 = "joe's place";
​​
char **paramV
​a​
lues = &value1;
​ /* closer match to the documentation's syntax */​

//char *paramValues[] = {"joe's place"}; /* same as above, different syntax
*/
//
//char *paramValues[1]; /* this looks to be missing */
//paramValues[0]="joe's place"; /* what you had */
res = PQexecParms(conn,
"SELECT * FROM test1 WHERE t = $1",
1, /* there is only 1 entry in paramValues array */
paramValues, /* address of parameter value array */
NULL, /* don't need param lengths since text */
NULL, /* defaul to all text params */
1); /* return all values as binary */

Well, you have an array of pointers to characters called paramValues. The
$1 refers to whatever is pointed to by paramValues[0]​, which is a pointer
to value1 which is a C "string". Basically in the second parameter, the
command, the $n is used as a 1-based index into the paramValues[] array.
This means that the actual C language array value is one less (since C
arrays are 0-based). Which means that "$n" (n>=1) in the "command" string
refers to value pointed to by paramValues[n-1]. The 3rd value, 1 in this
case, tells PQexecParms how many entries there are in the paramValues[]
array. I guess this is a type of validity check that the $n in the command
string is not too large for the array.

Note: please keep the discussion on the list, not to me personally. It may
be of help to others (or maybe not, I don't know.)

--
Heisenberg may have been here.

Unicode: http://xkcd.com/1726/

Maranatha! <><
John McKown

I think I understand.
When I use this in my code I get
"undefined reference to `PQexecParms'
when compiling.

references in main is

const char *conninfo; /* connection string to the database */
PGconn *conn; /* connection to the database */
PGresult *res; /* result of sql query */
int nFields; /* print out the attribute names */
int i; / * print the columns */
j;

Is the a reserved reference to use with

Reserved res = PQexecParms(conn.... )

Then I assume that I have to use another reference than res.

Thanks.

/Poul

2016-11-22 0:48 GMT+01:00 John McKown <john.archie.mckown@gmail.com>:

On Mon, Nov 21, 2016 at 11:22 AM, Poul Kristensen <bcc5226@gmail.com>
wrote:

Thank you for fast repons!

The $1 substitution below. I assume that it refers to "joe's place". But
it is not very clear to me, how "joe's place" will appear instead of $1
when running. Where is it possiible to read more about this? There just
is'nt much about substitution in C online. Any recommended books to buy?

/* Here is our out-of-line parameter value */
paramValues[0] = "joe's place";

res = PQexecParams(conn,
"SELECT * FROM test1 WHERE t = $1",
1, /* one param */
NULL, /* let the backend deduce param type */
paramValues,
NULL, /* don't need param lengths since text */
NULL, /* default to all text params */
1); /* ask for binary results */
}

/Poul

​It is described better here: https://www.postgresql.org/
docs/9.6/static/libpq-exec.html
than I can do. But I just noticed a mistake in your code, or maybe just
something left out. I would say:

char *value1 = "joe's place";
​​
char **paramV
​a​
lues = &value1;
​ /* closer match to the documentation's syntax */​

//char *paramValues[] = {"joe's place"}; /* same as above, different
syntax */
//
//char *paramValues[1]; /* this looks to be missing */
//paramValues[0]="joe's place"; /* what you had */
res = PQexecParms(conn,
"SELECT * FROM test1 WHERE t = $1",
1, /* there is only 1 entry in paramValues array */
paramValues, /* address of parameter value array */
NULL, /* don't need param lengths since text */
NULL, /* defaul to all text params */
1); /* return all values as binary */

Well, you have an array of pointers to characters called paramValues. The
$1 refers to whatever is pointed to by paramValues[0]​, which is a pointer
to value1 which is a C "string". Basically in the second parameter, the
command, the $n is used as a 1-based index into the paramValues[] array.
This means that the actual C language array value is one less (since C
arrays are 0-based). Which means that "$n" (n>=1) in the "command" string
refers to value pointed to by paramValues[n-1]. The 3rd value, 1 in this
case, tells PQexecParms how many entries there are in the paramValues[]
array. I guess this is a type of validity check that the $n in the command
string is not too large for the array.

Note: please keep the discussion on the list, not to me personally. It may
be of help to others (or maybe not, I don't know.)

--
Heisenberg may have been here.

Unicode: http://xkcd.com/1726/

Maranatha! <><
John McKown

--
Med venlig hilsen / Best regards
Poul Kristensen
Linux-OS/Virtualizationexpert and Oracle DBA

Poul Kristensen <bcc5226@gmail.com> writes:

When I use this in my code I get
"undefined reference to `PQexecParms'
when compiling.

IIRC, it's PQexecParams not PQexecParms

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Tue, Nov 22, 2016 at 8:22 AM, Poul Kristensen <bcc5226@gmail.com> wrote:

I think I understand.
When I use this in my code I get
"undefined reference to `PQexecParms'

​The correct name is PQexecParams (note the last "a"). Sorry I missed that
when first looking.​ Also, just to be sure, did you include the argument
"-lpq" on the compile command to point to the PostgreSQL library for
linking?

when compiling.

references in main is

const char *conninfo; /* connection string to the database */
PGconn *conn; /* connection to the database */
PGresult *res; /* result of sql query */
int nFields; /* print out the attribute names */
int i; / * print the columns */
j;

Is the a reserved reference to use with

Reserved res = PQexecParms(conn.... )

Then I assume that I have to use another reference than res.

Thanks.

/Poul

--
Heisenberg may have been here.

Unicode: http://xkcd.com/1726/

Maranatha! <><
John McKown

The below mentioned pg_database does not appear in $1.
In the postgresql.log I get "select datname from $1".
Do you have a suggestion of how to solve this?

I get double declarations when using both
char *paramValues[1]
char *paramValues[1]

//char *value1 = "pg_database";
//char **paramValues = &value1; /* closer match to the documentation's
syntax */
char *paramValues[1] = {"pg_database"}; /* same as above, different syntax
*/
//
//char *paramValues[1]; /* this looks to be missing */
//paramValues[0]={"pg_database"}; /* what you had */
res = PQexecParams(conn,
"select datname from $1",
1, /* there is only 1 entry in paramValues array */
NULL,
paramValues, /* address of parameter value array */
NULL, /* don't need param lengths since text */
NULL, /* defaul to all text params */
1); /* return all values as binary */

Thanks.

/Poul

2016-11-22 15:50 GMT+01:00 John McKown <john.archie.mckown@gmail.com>:

On Tue, Nov 22, 2016 at 8:22 AM, Poul Kristensen <bcc5226@gmail.com>
wrote:

I think I understand.
When I use this in my code I get
"undefined reference to `PQexecParms'

​The correct name is PQexecParams (note the last "a"). Sorry I missed that
when first looking.​ Also, just to be sure, did you include the argument
"-lpq" on the compile command to point to the PostgreSQL library for
linking?

when compiling.

references in main is

const char *conninfo; /* connection string to the database */
PGconn *conn; /* connection to the database */
PGresult *res; /* result of sql query */
int nFields; /* print out the attribute names */
int i; / * print the columns */
j;

Is the a reserved reference to use with

Reserved res = PQexecParms(conn.... )

Then I assume that I have to use another reference than res.

Thanks.

/Poul

--
Heisenberg may have been here.

Unicode: http://xkcd.com/1726/

Maranatha! <><
John McKown

--
Med venlig hilsen / Best regards
Poul Kristensen
Linux-OS/Virtualizationexpert and Oracle DBA

On Wed, Nov 23, 2016 at 3:46 AM, Poul Kristensen <bcc5226@gmail.com> wrote:

In the postgresql.log I get "select datname from $1".

​That is expected when you parameterize a query - the query string includes
parameter​s instead of values. What you are seeing is the "Prepare"
component of a "prepare-execute". When you execute you then see something
like; "execute unnamed_query ['pg_database']" (note, I'm not familiar with
the exact syntax and terminology that would appear in the log file).

David J.

"David G. Johnston" <david.g.johnston@gmail.com> writes:

On Wed, Nov 23, 2016 at 3:46 AM, Poul Kristensen <bcc5226@gmail.com> wrote:

In the postgresql.log I get "select datname from $1".

​That is expected when you parameterize a query - the query string includes
parameter​s instead of values.

It looks like Poul is hoping to use a parameter as a table name,
which doesn't work. Parameters can only represent values (constants).

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

My goal is to grap a user from the OS and and substitute the grapped user -
to be the owner of the database - in the sql commands
e.g using shell-script substitution syntax:
create user ${user} with login;
create tablespace ${user} location '/path/${user)';
create database ${user} owner ${user} on tablespace ${user};
I would be surpriced if this should not be possible using gnu gcc but I
might be wrong. It is just a question of figurering out way to do this.
http://zetcode.com/db/postgresqlc/ has an example of how to do this using
integer as argument on
the commandline.
Do you have a hint of the substitution syntax to be used in gnu gcc.

Thanks.

/Poul

2016-11-23 16:44 GMT+01:00 Tom Lane <tgl@sss.pgh.pa.us>:

"David G. Johnston" <david.g.johnston@gmail.com> writes:

On Wed, Nov 23, 2016 at 3:46 AM, Poul Kristensen <bcc5226@gmail.com>

wrote:

In the postgresql.log I get "select datname from $1".

​That is expected when you parameterize a query - the query string

includes

parameter​s instead of values.

It looks like Poul is hoping to use a parameter as a table name,
which doesn't work. Parameters can only represent values (constants).

regards, tom lane

--
Med venlig hilsen / Best regards
Poul Kristensen
Linux-OS/Virtualizationexpert and Oracle DBA

Solution:

C is able to compile with an embedded shell script.
So a small C program with an argument being the user passed to the shell
script it succeded succesfully.
I am aware that it could be my question was not specifically relevant to
Postgresql! :)
It took a while to solve. :)

Thanks

Poul

2016-11-23 21:31 GMT+01:00 Poul Kristensen <bcc5226@gmail.com>:

My goal is to grap a user from the OS and and substitute the grapped user
- to be the owner of the database - in the sql commands
e.g using shell-script substitution syntax:
create user ${user} with login;
create tablespace ${user} location '/path/${user)';
create database ${user} owner ${user} on tablespace ${user};
I would be surpriced if this should not be possible using gnu gcc but I
might be wrong. It is just a question of figurering out way to do this.
http://zetcode.com/db/postgresqlc/ has an example of how to do this using
integer as argument on
the commandline.
Do you have a hint of the substitution syntax to be used in gnu gcc.

Thanks.

/Poul

2016-11-23 16:44 GMT+01:00 Tom Lane <tgl@sss.pgh.pa.us>:

"David G. Johnston" <david.g.johnston@gmail.com> writes:

On Wed, Nov 23, 2016 at 3:46 AM, Poul Kristensen <bcc5226@gmail.com>

wrote:

In the postgresql.log I get "select datname from $1".

​That is expected when you parameterize a query - the query string

includes

parameter​s instead of values.

It looks like Poul is hoping to use a parameter as a table name,
which doesn't work. Parameters can only represent values (constants).

regards, tom lane

--
Med venlig hilsen / Best regards
Poul Kristensen
Linux-OS/Virtualizationexpert and Oracle DBA

--
Med venlig hilsen / Best regards
Poul Kristensen
Linux-OS/Virtualizationexpert and Oracle DBA