Обсуждение: psql access of user's environmental variables
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
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
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:
--------------------------------------------------------------------------------------------------
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
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: > 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 >
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
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-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 > > >
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
geomIS 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
>
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:
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 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 EXECUTEhttp://www.postgresql.org/docs/8.2/interactive/plpgsql-stateme nts.html#PLPGSQL-STATEMENTS-EXECUTING-DYNBest 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 32349Geschäftsführung: Werner Klötsch, Marco de Gast ________________________________ From: pgsql-general-owner@postgresql.org[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Thorsten KrausSent: Thursday, May 03, 2007 5:00 PMTo: pgsql-general@postgresql.orgSubject: [GENERAL] Stored procedureHi NG,I want to write a stored procedure which creates atable 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 Ihave 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/
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
youknow 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
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')
||'::textOR 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
thepicture, 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/