psql access of user's environmental variables

Started by Paul Tillesalmost 19 years ago9 messagesgeneral

Jump to latest

Paul Tilles

Paul.Tilles@noaa.gov

almost 19 years ago

I need to use the value of an environment variable as part of an SQL
query within psql.

I can do the following withing psql:

\set local_site `echo $FXA_LOCAL_SITE

\echo local site = :local_site

The result is "local_site = xxx" which is correct.

What I really want to do is the following:

\set local_site `echo $FXA_LOCAL_SITE

UPDATE table_name SET office_id = :local_site;

This results in the message

column "xxx" does not exist

Is there any way that I can use the value of the FXA_LOCAL_SITE env
variable in my UPDATE statement?

Paul Tilles

Tom Lane

tgl@sss.pgh.pa.us

almost 19 years ago

In reply to: Paul Tilles (#1)

Re: psql access of user's environmental variables

Paul Tilles <Paul.Tilles@noaa.gov> writes:

What I really want to do is the following:

\set local_site `echo $FXA_LOCAL_SITE
UPDATE table_name SET office_id = :local_site;

This results in the message
column "xxx" does not exist

Yes, because you have no quotes in the value of the variable, so that
update looks to the server like
UPDATE table_name SET office_id = xxx;

After some fooling around, the easiest way to get the needed quotes is
to embed them in the echo result:

\set local_site `echo "'$FXA_LOCAL_SITE'"`

BTW, does your psql really let you leave off the trailing ` like
that? Mine doesn't.

regards, tom lane

Thorsten Kraus

TK-Spam@gmx.de

almost 19 years ago

In reply to: Tom Lane (#2)

Stored procedure

Hi NG,

I want to write a stored procedure which creates a table in my
PostgreSQL database. The procedure has one input parameter: the table name.
Here is my first try, but that does not work:
--------------------------------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION create_geom_table(text) RETURNS void AS $$
DECLARE
--table_name TEXT;
BEGIN
---------------------------------------
CREATE TABLE table_name
(
id integer,
"time" timestamp without time zone,
geom geometry,
CONSTRAINT enforce_dims_geom CHECK (ndims(geom) = 2),
CONSTRAINT enforce_geotype_geom CHECK (geometrytype(geom) =
'MULTIPOLYGON'::text OR geom IS NULL),
CONSTRAINT enforce_srid_geom CHECK (srid(geom) = -1)
)
WITHOUT OIDS;
ALTER TABLE table_name OWNER TO "admin";

CREATE INDEX geo_index ON table_name USING gist(geom);

---------------------------------------
ALTER FUNCTION create_geom_table(table_name) OWNER TO "admin";
END;
$$ LANGUAGE plpgsql;

--------------------------------------------------------------------------------------------------

Can someone tell me what's wrong with this and what I have to change?

Regards,
Thorsten

Paul Tilles

Paul.Tilles@noaa.gov

almost 19 years ago

In reply to: Tom Lane (#2)

Re: psql access of user's environmental variables

Tom,
Thank you. That works.

My psql does not allow me to leave off the trailing `.

It is my typing that is the problem.

Paul

Tom Lane wrote:

Show quoted text

Paul Tilles <Paul.Tilles@noaa.gov> writes:

What I really want to do is the following:

\set local_site `echo $FXA_LOCAL_SITE
UPDATE table_name SET office_id = :local_site;

This results in the message
column "xxx" does not exist

Yes, because you have no quotes in the value of the variable, so that
update looks to the server like
UPDATE table_name SET office_id = xxx;

After some fooling around, the easiest way to get the needed quotes is
to embed them in the echo result:

\set local_site `echo "'$FXA_LOCAL_SITE'"`

BTW, does your psql really let you leave off the trailing ` like
that? Mine doesn't.

regards, tom lane

Hakan Kocaman

Hakan.Kocaman@digame.de

almost 19 years ago

In reply to: Thorsten Kraus (#3)

Re: Stored procedure

Hi,

Try EXECUTE
http://www.postgresql.org/docs/8.2/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

Best Regards

Hakan Kocaman
Software-Development

digame.de GmbH
Richard-Byrd-Str. 4-8
50829 Köln

Tel.: +49 (0) 221 59 68 88 31
Fax: +49 (0) 221 59 68 88 98
Email: hakan.kocaman@digame.de

digame.de GmbH, Sitz der Gesellschaft: Köln, Handelsregister Köln, HRB 32349
Geschäftsführung: Werner Klötsch, Marco de Gast

________________________________

From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Thorsten Kraus
Sent: Thursday, May 03, 2007 5:00 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Stored procedure

Hi NG,

I want to write a stored procedure which creates a table in my PostgreSQL database. The procedure has one input parameter: the table name.
Here is my first try, but that does not work:
--------------------------------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION create_geom_table(text) RETURNS void AS $$
DECLARE
--table_name TEXT;
BEGIN
---------------------------------------
CREATE TABLE table_name
(
id integer,
"time" timestamp without time zone,
geom geometry,
CONSTRAINT enforce_dims_geom CHECK (ndims(geom) = 2),
CONSTRAINT enforce_geotype_geom CHECK (geometrytype(geom) = 'MULTIPOLYGON'::text OR geom IS NULL),
CONSTRAINT enforce_srid_geom CHECK (srid(geom) = -1)
)
WITHOUT OIDS;
ALTER TABLE table_name OWNER TO "admin";

CREATE INDEX geo_index ON table_name USING gist(geom);

---------------------------------------
ALTER FUNCTION create_geom_table(table_name) OWNER TO "admin";
END;
$$ LANGUAGE plpgsql;

--------------------------------------------------------------------------------------------------

Can someone tell me what's wrong with this and what I have to change?

Regards,
Thorsten

Thorsten Kraus

TK-Spam@gmx.de

almost 19 years ago

In reply to: Hakan Kocaman (#5)

Re: Stored procedure

Hi,

thanks for your answer, but I don't get the point. Perhaps you can give
me a small example how to get the EXECUTE into a stored procedure.

Regards

Hakan Kocaman schrieb:

Show quoted text

Hi,

Try EXECUTE
http://www.postgresql.org/docs/8.2/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

Best Regards

Hakan Kocaman
Software-Development

digame.de GmbH
Richard-Byrd-Str. 4-8
50829 Kï¿½ln

Tel.: +49 (0) 221 59 68 88 31
Fax: +49 (0) 221 59 68 88 98
Email: hakan.kocaman@digame.de

digame.de GmbH, Sitz der Gesellschaft: Kï¿½ln, Handelsregister Kï¿½ln, HRB 32349
Geschï¿½ftsfï¿½hrung: Werner Klï¿½tsch, Marco de Gast

________________________________

From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Thorsten Kraus
Sent: Thursday, May 03, 2007 5:00 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Stored procedure

Hi NG,

I want to write a stored procedure which creates a table in my PostgreSQL database. The procedure has one input parameter: the table name.
Here is my first try, but that does not work:
--------------------------------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION create_geom_table(text) RETURNS void AS $$
DECLARE
--table_name TEXT;
BEGIN
---------------------------------------
CREATE TABLE table_name
(
id integer,
"time" timestamp without time zone,
geom geometry,
CONSTRAINT enforce_dims_geom CHECK (ndims(geom) = 2),
CONSTRAINT enforce_geotype_geom CHECK (geometrytype(geom) = 'MULTIPOLYGON'::text OR geom IS NULL),
CONSTRAINT enforce_srid_geom CHECK (srid(geom) = -1)
)
WITHOUT OIDS;
ALTER TABLE table_name OWNER TO "admin";

CREATE INDEX geo_index ON table_name USING gist(geom);

---------------------------------------
ALTER FUNCTION create_geom_table(table_name) OWNER TO "admin";
END;
$$ LANGUAGE plpgsql;

--------------------------------------------------------------------------------------------------

Can someone tell me what's wrong with this and what I have to change?

Regards,
Thorsten

Hakan Kocaman

Hakan.Kocaman@digame.de

almost 19 years ago

In reply to: Thorsten Kraus (#6)

Re: Stored procedure

Hi,

your example should look like this:
CREATE OR REPLACE FUNCTION create_geom_table(table_name text) RETURNS void AS $BODY$
DECLARE
func_text text;
BEGIN

func_text:='DROP TABLE ' || table_name ||';
CREATE TABLE ' || table_name ||'
(
id integer,
mytimestamp timestamp without time zone--,
--geom geometry,
--CONSTRAINT enforce_dims_geom CHECK (ndims(geom) = 2),
--CONSTRAINT enforce_geotype_geom CHECK (geometrytype(geom) = '|| quote_literal('MULTIPOLYGON') ||'::text OR geom IS NULL),
--CONSTRAINT enforce_srid_geom CHECK (srid(geom) = -1)
)
WITHOUT OIDS;
ALTER TABLE ' || quote_literal(table_name) ||'OWNER TO "admin";

--CREATE INDEX geo_index ON '|| quote_literal(table_name) ||'USING gist(geom);

--ALTER FUNCTION create_geom_table('|| quote_literal(table_name) ||') OWNER TO "admin";
';
EXECUTE func_text;
END;
$BODY$ LANGUAGE plpgsql;

select create_geom_table('test_geom_tbl');

It's not exactly the same, hence i don't got some of yout types(geom for example) laying around, but you get the picture, no?

Best regards

Hakan Kocaman
Software-Development

digame.de GmbH
Richard-Byrd-Str. 4-8
50829 Köln

Tel.: +49 (0) 221 59 68 88 31
Fax: +49 (0) 221 59 68 88 98
Email: hakan.kocaman@digame.de

digame.de GmbH, Sitz der Gesellschaft: Köln, Handelsregister Köln, HRB 32349
Geschäftsführung: Werner Klötsch, Marco de Gast

Show quoted text

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of
Thorsten Kraus
Sent: Thursday, May 03, 2007 5:27 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Stored procedure

Hi,

thanks for your answer, but I don't get the point. Perhaps
you can give
me a small example how to get the EXECUTE into a stored procedure.

Regards

Hakan Kocaman schrieb:

Hi,

Try EXECUTE

http://www.postgresql.org/docs/8.2/interactive/plpgsql-stateme
nts.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

Best Regards

Hakan Kocaman
Software-Development

digame.de GmbH
Richard-Byrd-Str. 4-8
50829 Köln

Tel.: +49 (0) 221 59 68 88 31
Fax: +49 (0) 221 59 68 88 98
Email: hakan.kocaman@digame.de

digame.de GmbH, Sitz der Gesellschaft: Köln,

Handelsregister Köln, HRB 32349

Geschäftsführung: Werner Klötsch, Marco de Gast

________________________________

From: pgsql-general-owner@postgresql.org

[mailto:pgsql-general-owner@postgresql.org] On Behalf Of
Thorsten Kraus

Sent: Thursday, May 03, 2007 5:00 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Stored procedure

Hi NG,

I want to write a stored procedure which creates a

table in my PostgreSQL database. The procedure has one input
parameter: the table name.

Here is my first try, but that does not work:

--------------------------------------------------------------
------------------------------------

CREATE OR REPLACE FUNCTION create_geom_table(text)

RETURNS void AS $$

DECLARE
--table_name TEXT;
BEGIN
---------------------------------------
CREATE TABLE table_name
(
id integer,
"time" timestamp without time zone,
geom geometry,
CONSTRAINT enforce_dims_geom CHECK (ndims(geom) = 2),
CONSTRAINT enforce_geotype_geom CHECK

(geometrytype(geom) = 'MULTIPOLYGON'::text OR geom IS NULL),

CONSTRAINT enforce_srid_geom CHECK (srid(geom) = -1)
)
WITHOUT OIDS;
ALTER TABLE table_name OWNER TO "admin";

CREATE INDEX geo_index ON table_name USING gist(geom);

---------------------------------------
ALTER FUNCTION create_geom_table(table_name) OWNER TO "admin";
END;
$$ LANGUAGE plpgsql;

--------------------------------------------------------------
------------------------------------

Can someone tell me what's wrong with this and what I

have to change?

Regards,
Thorsten

---------------------------(end of
broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Thorsten Kraus

TK-Spam@gmx.de

almost 19 years ago

In reply to: Hakan Kocaman (#7)

Re: Stored procedure

Hi,

thank you for your detailled answer!
Today I had the possibility to test it in the office. The procedure
could be stored.
But when I call it SELECT create_geom_table('testtable') Then an error
occurs: column testtable not available. Do you know why?

Regards

Hakan Kocaman schrieb:

Show quoted text

Hi,

your example should look like this:
CREATE OR REPLACE FUNCTION create_geom_table(table_name text) RETURNS void AS $BODY$
DECLARE
func_text text;
BEGIN

func_text:='DROP TABLE ' || table_name ||';
CREATE TABLE ' || table_name ||'
(
id integer,
mytimestamp timestamp without time zone--,
--geom geometry,
--CONSTRAINT enforce_dims_geom CHECK (ndims(geom) = 2),
--CONSTRAINT enforce_geotype_geom CHECK (geometrytype(geom) = '|| quote_literal('MULTIPOLYGON') ||'::text OR geom IS NULL),
--CONSTRAINT enforce_srid_geom CHECK (srid(geom) = -1)
)
WITHOUT OIDS;
ALTER TABLE ' || quote_literal(table_name) ||'OWNER TO "admin";

--CREATE INDEX geo_index ON '|| quote_literal(table_name) ||'USING gist(geom);

--ALTER FUNCTION create_geom_table('|| quote_literal(table_name) ||') OWNER TO "admin";
';
EXECUTE func_text;
END;
$BODY$ LANGUAGE plpgsql;

select create_geom_table('test_geom_tbl');

It's not exactly the same, hence i don't got some of yout types(geom for example) laying around, but you get the picture, no?

Best regards

Hakan Kocaman
Software-Development

digame.de GmbH
Richard-Byrd-Str. 4-8
50829 Kï¿½ln

Tel.: +49 (0) 221 59 68 88 31
Fax: +49 (0) 221 59 68 88 98
Email: hakan.kocaman@digame.de

digame.de GmbH, Sitz der Gesellschaft: Kï¿½ln, Handelsregister Kï¿½ln, HRB 32349
Geschï¿½ftsfï¿½hrung: Werner Klï¿½tsch, Marco de Gast

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of
Thorsten Kraus
Sent: Thursday, May 03, 2007 5:27 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Stored procedure

Hi,

thanks for your answer, but I don't get the point. Perhaps
you can give
me a small example how to get the EXECUTE into a stored procedure.

Regards

Hakan Kocaman schrieb:

Hi,

Try EXECUTE

http://www.postgresql.org/docs/8.2/interactive/plpgsql-stateme
nts.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

Best Regards

Hakan Kocaman
Software-Development

digame.de GmbH
Richard-Byrd-Str. 4-8
50829 Kï¿½ln

Tel.: +49 (0) 221 59 68 88 31
Fax: +49 (0) 221 59 68 88 98
Email: hakan.kocaman@digame.de

digame.de GmbH, Sitz der Gesellschaft: Kï¿½ln,

Handelsregister Kï¿½ln, HRB 32349

Geschï¿½ftsfï¿½hrung: Werner Klï¿½tsch, Marco de Gast

________________________________

From: pgsql-general-owner@postgresql.org

[mailto:pgsql-general-owner@postgresql.org] On Behalf Of
Thorsten Kraus

Sent: Thursday, May 03, 2007 5:00 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Stored procedure

Hi NG,

I want to write a stored procedure which creates a

table in my PostgreSQL database. The procedure has one input
parameter: the table name.

Here is my first try, but that does not work:

--------------------------------------------------------------
------------------------------------

CREATE OR REPLACE FUNCTION create_geom_table(text)

RETURNS void AS $$

DECLARE
--table_name TEXT;
BEGIN
---------------------------------------
CREATE TABLE table_name
(
id integer,
"time" timestamp without time zone,
geom geometry,
CONSTRAINT enforce_dims_geom CHECK (ndims(geom) = 2),
CONSTRAINT enforce_geotype_geom CHECK

(geometrytype(geom) = 'MULTIPOLYGON'::text OR geom IS NULL),

CONSTRAINT enforce_srid_geom CHECK (srid(geom) = -1)
)
WITHOUT OIDS;
ALTER TABLE table_name OWNER TO "admin";

CREATE INDEX geo_index ON table_name USING gist(geom);

---------------------------------------
ALTER FUNCTION create_geom_table(table_name) OWNER TO "admin";
END;
$$ LANGUAGE plpgsql;

--------------------------------------------------------------
------------------------------------

Can someone tell me what's wrong with this and what I

have to change?

Regards,
Thorsten

---------------------------(end of
broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/

Hakan Kocaman

Hakan.Kocaman@digame.de

almost 19 years ago

In reply to: Thorsten Kraus (#8)

Re: Stored procedure

Hi,

could you please post the complete code that you used to create the function.

It sounds suspicously, that pg thinks 'testtable' is a coloum.
Have you set proper quotes in your function-code?
Maybe i got some mistakes regarding the usage of quote_literal in my sample code.

Till later

Hakan Kocaman
Software-Development

digame.de GmbH
Richard-Byrd-Str. 4-8
50829 Köln

Tel.: +49 (0) 221 59 68 88 31
Fax: +49 (0) 221 59 68 88 98
Email: hakan.kocaman@digame.de

digame.de GmbH, Sitz der Gesellschaft: Köln, Handelsregister Köln, HRB 32349
Geschäftsführung: Werner Klötsch, Marco de Gast

________________________________

From: Thorsten Kraus [mailto:TK-Spam@gmx.de]
Sent: Friday, May 04, 2007 5:36 PM
To: Hakan Kocaman; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Stored procedure

Hi,

thank you for your detailled answer!
Today I had the possibility to test it in the office. The procedure could be stored.
But when I call it SELECT create_geom_table('testtable') Then an error occurs: column testtable not available. Do you know why?

Regards

Hakan Kocaman schrieb:

Hi,

your example should look like this:
CREATE OR REPLACE FUNCTION create_geom_table(table_name text) RETURNS void AS $BODY$
DECLARE
func_text text;
BEGIN

--CREATE INDEX geo_index ON '|| quote_literal(table_name) ||'USING gist(geom);

--ALTER FUNCTION create_geom_table('|| quote_literal(table_name) ||') OWNER TO "admin";
';
EXECUTE func_text;
END;
$BODY$ LANGUAGE plpgsql;

select create_geom_table('test_geom_tbl');

It's not exactly the same, hence i don't got some of yout types(geom for example) laying around, but you get the picture, no?

Best regards

Hakan Kocaman
Software-Development

digame.de GmbH
Richard-Byrd-Str. 4-8
50829 Köln

Tel.: +49 (0) 221 59 68 88 31
Fax: +49 (0) 221 59 68 88 98
Email: hakan.kocaman@digame.de

digame.de GmbH, Sitz der Gesellschaft: Köln, Handelsregister Köln, HRB 32349
Geschäftsführung: Werner Klötsch, Marco de Gast

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of
Thorsten Kraus
Sent: Thursday, May 03, 2007 5:27 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Stored procedure

Hi,

thanks for your answer, but I don't get the point. Perhaps
you can give
me a small example how to get the EXECUTE into a stored procedure.

Regards

Hakan Kocaman schrieb:

Hi,

Try EXECUTE

http://www.postgresql.org/docs/8.2/interactive/plpgsql-stateme
nts.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

Best Regards

Hakan Kocaman
Software-Development

digame.de GmbH
Richard-Byrd-Str. 4-8
50829 Köln

Tel.: +49 (0) 221 59 68 88 31
Fax: +49 (0) 221 59 68 88 98
Email: hakan.kocaman@digame.de

digame.de GmbH, Sitz der Gesellschaft: Köln,

Handelsregister Köln, HRB 32349

Geschäftsführung: Werner Klötsch, Marco de Gast

________________________________

From: pgsql-general-owner@postgresql.org

[mailto:pgsql-general-owner@postgresql.org] On Behalf Of
Thorsten Kraus

Sent: Thursday, May 03, 2007 5:00 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Stored procedure

Hi NG,

I want to write a stored procedure which creates a

table in my PostgreSQL database. The procedure has one input
parameter: the table name.

Here is my first try, but that does not work:

--------------------------------------------------------------
------------------------------------

CREATE OR REPLACE FUNCTION create_geom_table(text)

RETURNS void AS $$

DECLARE
--table_name TEXT;
BEGIN
---------------------------------------
CREATE TABLE table_name
(
id integer,
"time" timestamp without time zone,
geom geometry,
CONSTRAINT enforce_dims_geom CHECK (ndims(geom) = 2),
CONSTRAINT enforce_geotype_geom CHECK

(geometrytype(geom) = 'MULTIPOLYGON'::text OR geom IS NULL),

CONSTRAINT enforce_srid_geom CHECK (srid(geom) = -1)
)
WITHOUT OIDS;
ALTER TABLE table_name OWNER TO "admin";

CREATE INDEX geo_index ON table_name USING gist(geom);

---------------------------------------
ALTER FUNCTION create_geom_table(table_name) OWNER TO "admin";
END;
$$ LANGUAGE plpgsql;

--------------------------------------------------------------
------------------------------------

Can someone tell me what's wrong with this and what I

have to change?

Regards,
Thorsten

---------------------------(end of
broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/