Thread: some questions : psql

Hello

> 1- is it possible to set a default schema ?
>     i tried : set DEFAULT SCHEMA base1; but it didn't worked

SET search_path to base1;"

>
> 2- is it possible to have one file with global values and to use them when creating functions.
> one example is : i have a temporary directory, and several functions need to access this folder, so is it possible to
passthe path as with Oracle 
>     DEFINE TempDirectory = /home/....
>     @createMDNS.sql &TempDirectory

Yes. You can use commands and variables psql.

\set TempDirectory /home/... -- in file global.sql
\i global.sql

SELECT somefce(:TempDirectory)

>
>

more on http://developer.postgresql.org/docs/postgres/app-psql.html



> 3- my psql script create 2 tablespaces, an user, the database, 1 schema and the tables and functions
> but when i launch pgadmin, i see my new user, the tablespace and the database, but I don't see the schema and in
consequencethe tables and functions. 
> but all have been created because i can ask tables, but they don't appear and i don't know why...;
>

I don't know. I don't use pgadmin


Regards
Pavel Stehule

sorry, i badly explain my second problem (and it make me think about an other question :) )
what i really want to say is :
i would like to declare a global variable :
    DEFINE pool = 10
and use it into a function
    @createMDNS.sql &pool
and my file createMDNS.sql is
    create or replace package body MDXML_EXPORT
     as
       pool varchar2(150) := '&1';
        procedure toto(.......
    .....
    .....
    /
that what i wanted to explain

and my new question is :) :
i wrote a first file :
    \set databaseName 'base1'
    \set createMDXML '/home/toto/MDXML/execCreateMDXML.sql'
    \i :createMDXML
and in my second file, i need the name af the database but if i wrote this :
    SET search_path TO :databaseName;
it doesn't work....

regards


-----Message d'origine-----
De : Pavel Stehule [mailto:stehule@kix.fsv.cvut.cz]
Envoy� : mardi 3 mai 2005 14:30
� : FERREIRA, William (COFRAMI)
Cc : pgsql-general@postgresql.org
Objet : Re: [GENERAL] some questions : psql


Hello

> 1- is it possible to set a default schema ?
>     i tried : set DEFAULT SCHEMA base1; but it didn't worked

SET search_path to base1;"

>
> 2- is it possible to have one file with global values and to use them when creating functions.
> one example is : i have a temporary directory, and several functions need to access this folder, so is it possible to
passthe path as with Oracle 
>     DEFINE TempDirectory = /home/....
>     @createMDNS.sql &TempDirectory

Yes. You can use commands and variables psql.

\set TempDirectory /home/... -- in file global.sql
\i global.sql

SELECT somefce(:TempDirectory)

>
>

more on http://developer.postgresql.org/docs/postgres/app-psql.html



> 3- my psql script create 2 tablespaces, an user, the database, 1 schema and the tables and functions
> but when i launch pgadmin, i see my new user, the tablespace and the database, but I don't see the schema and in
consequencethe tables and functions. 
> but all have been created because i can ask tables, but they don't appear and i don't know why...;
>

I don't know. I don't use pgadmin


Regards
Pavel Stehule


This mail has originated outside your organization,
either from an external partner or the Global Internet.
Keep this in mind if you answer this message.

This e-mail is intended only for the above addressee. It may contain
privileged information. If you are not the addressee you must not copy,
distribute, disclose or use any of the information in it. If you have
received it in error please delete it and immediately notify the sender.
Security Notice: all e-mail, sent to or from this address, may be
accessed by someone other than the recipient, for system management and
security reasons. This access is controlled under Regulation of
Investigatory Powers Act 2000, Lawful Business Practises.

On Tue, 3 May 2005, FERREIRA, William (COFRAMI) wrote:

> sorry, i badly explain my second problem (and it make me think about an other question :) )
> what i really want to say is :
> i would like to declare a global variable :

There is one big difference. PostgreSQL don't know global variables. There
is only local variables in stored procedures or local variables of sql
monitor ~ psql. You cannot directly read psql's variables.

>
> and my new question is :) :
> i wrote a first file :
>     \set databaseName 'base1'
>     \set createMDXML '/home/toto/MDXML/execCreateMDXML.sql'
>     \i :createMDXML
> and in my second file, i need the name af the database but if i wrote this :
>     SET search_path TO :databaseName;
> it doesn't work....
>

I am sorry. I can't help.

create schema fx;
create table fx.foo(i integer);
select * from foo;
ERROR:  relation "foo" does not exist
\set sp fx
set search_path to :sp
intra=# \set sp 'fx'
intra=# set search_path to :sp;
SET
Time: 9,349 ms
intra=# select * from foo;
 i
---
(0 rows)

look to /home/toto/MDXML/execCreateMDXML.sql sqlscript. Works really well?

Pavel

the search_path works well

thanks a lot


-----Message d'origine-----
De : Pavel Stehule [mailto:stehule@kix.fsv.cvut.cz]
Envoyé : mardi 3 mai 2005 16:09
À : FERREIRA, William (COFRAMI)
Cc : pgsql-general@postgresql.org
Objet : Re: [GENERAL] some questions : psql


On Tue, 3 May 2005, FERREIRA, William (COFRAMI) wrote:

> sorry, i badly explain my second problem (and it make me think about an other question :) )
> what i really want to say is :
> i would like to declare a global variable :

There is one big difference. PostgreSQL don't know global variables. There
is only local variables in stored procedures or local variables of sql
monitor ~ psql. You cannot directly read psql's variables.

>
> and my new question is :) :
> i wrote a first file :
>     \set databaseName 'base1'
>     \set createMDXML '/home/toto/MDXML/execCreateMDXML.sql'
>     \i :createMDXML
> and in my second file, i need the name af the database but if i wrote this :
>     SET search_path TO :databaseName;
> it doesn't work....
>

I am sorry. I can't help.

create schema fx;
create table fx.foo(i integer);
select * from foo;
ERROR:  relation "foo" does not exist
\set sp fx
set search_path to :sp
intra=# \set sp 'fx'
intra=# set search_path to :sp;
SET
Time: 9,349 ms
intra=# select * from foo;
 i
---
(0 rows)

look to /home/toto/MDXML/execCreateMDXML.sql sqlscript. Works really well?

Pavel


This mail has originated outside your organization,
either from an external partner or the Global Internet.
Keep this in mind if you answer this message.

This mail has originated outside your organization, either from an external partner or the Global Internet. Keep this
inmind if you answer this message.