Re: [SQL] Executing plpgsql scripts using psql, is that possible?
On Jan 16, 2006, at 5:35 AM, Daniel CAUNE wrote:
I would like to write some administration plpgsql scripts that
populate some tables (dimension tables) and to execute them using
psql. I’m not sure that is possible with psql as it is with Oracle
sqlplus or SQL Server MSQuery:
If you want to execute a plpgsql function from a file using psql,
just call it with SELECT. So your file might have:
create or replace function my_function(params integer)
returns integer as $$
DECLARE
V_MyObjectID bigint;
BEGIN
V_MyObjectID := RegisterMyObject('a string', 'another string');
AddObjectProperty(V_MyObjectID, 'a string');
AddObjectProperty(V_MyObjectID, 'another string');
END;
$$ language plpgsql;
SELECT my_function(1);
and then psql -f script.sql my_db
John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] psql client: technique for applying default values to :variables?
Michael Glaesemann wrote:
>
> On Jan 14, 2006, at 23:54 , Jeff Boes wrote:
>
>> Tony Wasson wrote:
>
>>
>> Sure, but that was not my question. I want to be able to set the
>> variable on the command line, BUT have it default to a value inside
>> the SQL script if not present on the command line.
>
> You could write a wrapper script that would accept a command line
> argument and load the file with the appropriate SET statement prepended.
Which really gains me nothing over just:
$ psql -f myscript.sql -v "MYVAR=${myvar:-default}"
The original question was: how can I store the default value in the
"myscript.sql" script where it's used, rather than have it in a calling
script? The aim here was to put the default in the code, so they would
be bound together...
--
Jeffery Boes <><
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 6: explain analyze is your friend
[SQL] For update
Hello, What does this do exactly: select * from where for update; Will be locked all the tuples in all tables it retrieves? In 8.0.5 this did not work for me. Thanks, Otto
Re: [SQL] psql client: technique for applying default values to :variables?
Jeff Boes writes:
> Michael Glaesemann wrote:
>> You could write a wrapper script that would accept a command line
>> argument and load the file with the appropriate SET statement prepended.
>
> Which really gains me nothing over just:
>
> $ psql -f myscript.sql -v "MYVAR=${myvar:-default}"
>
> The original question was: how can I store the default value in the
> "myscript.sql" script where it's used, rather than have it in a calling
> script? The aim here was to put the default in the code, so they would
> be bound together...
How about using the traditional Unix macro processor "m4" instead of
psql's variables?
E.g., you could write a myscript.sql.m4 file with m4 variables instead
of psql ones. You could then use ifdef() to test for previous
definitions, or even define a default()-wrapper macro.
regards,
Andreas
--
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] For update
Ottó Havasvölgyi wrote: > What does this do exactly: > > select * from where for update; > > Will be locked all the tuples in all tables it retrieves? Yes. > In 8.0.5 this did not work for me. How did it not work? Was any error message emitted? What is the view definition? -- Alvaro Herrera Developer, http://www.PostgreSQL.org "MySQL is a toy compared to PostgreSQL." (Randal L. Schwartz) (http://archives.postgresql.org/pgsql-general/2005-07/msg00517.php) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] For update
On Mon, Jan 16, 2006 at 02:05:15PM -0300, Alvaro Herrera wrote: > Ottó Havasvölgyi wrote: > > What does this do exactly: > > > > select * from where for update; > > > > Will be locked all the tuples in all tables it retrieves? > > Yes. > > > In 8.0.5 this did not work for me. > > How did it not work? Was any error message emitted? What is the view > definition? I wonder if "not work" means "didn't lock the rows" and the cause is simply not having a surrounding transaction. -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] best way for constants in the database
anybody have a good way to impliment constants in the database? using an immutable pgpgsql function, constant_name() - works, but not necessarily very efficient. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] best way for constants in the database
chester c young wrote: > anybody have a good way to impliment constants in the database? > > using an immutable pgpgsql function, constant_name() - works, but not > necessarily very efficient. A function declared IMMUTABLE should be efficient enough; it'll usually be evaluated once per query at most. The real answer is, as usual, "it depends." What're you trying to accomplish with a constant? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] For update
Hello, Huh, it was weeks ago, and I thought it was normal. It was a simple view with one or two inner joins and without aggragates. Perhaps somebody could try it. I got an error message, that said I cannot do that. I cannot remember the exact message unfortunately. Best Regards, Otto 2006/1/16, Alvaro Herrera <[EMAIL PROTECTED]>: Ottó Havasvölgyi wrote:> What does this do exactly:>> select * from where for update; >> Will be locked all the tuples in all tables it retrieves?Yes.> In 8.0.5 this did not work for me.How did it not work? Was any error message emitted? What is the viewdefinition? --Alvaro Herrera Developer, http://www.PostgreSQL.org"MySQL is a toy compared to PostgreSQL." (Randal L. Schwartz) (http://archives.postgresql.org/pgsql-general/2005-07/msg00517.php )
Re: [SQL] For update
Ottó Havasvölgyi wrote: > Hello, > > Huh, it was weeks ago, and I thought it was normal. It was a simple view > with one or two inner joins and without aggragates. Perhaps somebody could > try it. I got an error message, that said I cannot do that. I cannot > remember the exact message unfortunately. Sure, I can try it. Show us the definition and tell us what version are you using. -- Alvaro Herrerahttp://www.PlanetPostgreSQL.org "People get annoyed when you try to debug them." (Larry Wall) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] For update
Hello, Sorry, I have just tried it, and works with 8.0.5. I had missed something then. :( I like PostgreSQL. :) Thanks, Otto 2006/1/16, Alvaro Herrera <[EMAIL PROTECTED]>: Ottó Havasvölgyi wrote:> Hello,>> Huh, it was weeks ago, and I thought it was normal. It was a simple view > with one or two inner joins and without aggragates. Perhaps somebody could> try it. I got an error message, that said I cannot do that. I cannot> remember the exact message unfortunately.Sure, I can try it. Show us the definition and tell us what version are you using.--Alvaro Herrerahttp://www.PlanetPostgreSQL.org"People get annoyed when you try to debug them." (Larry Wall)
Re: [SQL] For update
Hello, Oh, I got it. Now in 8.0.6 When the view has UNION ALL, then PostgreSQL reports this: SELECT FOR UPDATE is not allowed in subqueries . I see why: select * from for update; After extraction: select * from UNION ALL for update; In this case it seems as if "for update" is in subquery2. How can this be tricked? Thanks, Otto 2006/1/16, Ottó Havasvölgyi <[EMAIL PROTECTED]>: Hello, Sorry, I have just tried it, and works with 8.0.5. I had missed something then. :( I like PostgreSQL. :) Thanks, Otto 2006/1/16, Alvaro Herrera <[EMAIL PROTECTED]>: Ottó Havasvölgyi wrote:> Hello,>> Huh, it was weeks ago, and I thought it was normal. It was a simple view > with one or two inner joins and without aggragates. Perhaps somebody could> try it. I got an error message, that said I cannot do that. I cannot> remember the exact message unfortunately.Sure, I can try it. Show us the definition and tell us what version are you using.--Alvaro Herrerahttp://www.PlanetPostgreSQL.org "People get annoyed when you try to debug them." (Larry Wall)
Re: [SQL] Executing plpgsql scripts using psql, is that possible?
> -Message d'origine-
> De : [EMAIL PROTECTED] [mailto:pgsql-sql-
> [EMAIL PROTECTED] De la part de John DeSoi
> Envoyé : lundi 16 janvier 2006 08:51
> À : Daniel CAUNE
> Cc : pgsql-sql@postgresql.org
> Objet : Re: [SQL] Executing plpgsql scripts using psql, is that possible?
>
>
> On Jan 16, 2006, at 5:35 AM, Daniel CAUNE wrote:
>
> > I would like to write some administration plpgsql scripts that
> > populate some tables (dimension tables) and to execute them using
> > psql. I’m not sure that is possible with psql as it is with Oracle
> > sqlplus or SQL Server MSQuery:
>
>
> If you want to execute a plpgsql function from a file using psql,
> just call it with SELECT. So your file might have:
>
> create or replace function my_function(params integer)
> returns integer as $$
> DECLARE
>V_MyObjectID bigint;
> BEGIN
>V_MyObjectID := RegisterMyObject('a string', 'another string');
>AddObjectProperty(V_MyObjectID, 'a string');
>AddObjectProperty(V_MyObjectID, 'another string');
>
> END;
> $$ language plpgsql;
>
>
> SELECT my_function(1);
>
>
> and then psql -f script.sql my_db
>
Yes, but that requires creating a function while I would prefer not having do
so, as I said in my previous mail: "I mean, without creating a function that
wraps the whole, of course! :-)". Why? Actually this is not a function; this
is a script that inserts static data into dimension tables such as Country,
Language, etc.
I have several scripts responsible for creating the database and all the
objects (tables, views, constraints, indexes, user-defined functions, etc.) of
my project. I would like to have some other scripts to initialize dimension
tables, i.e. inserting static data in those tables. The idea is to automate
the whole creation and initialization of a database on a PostgreSQL server; I
already have an Ant task that searches for SQL files, orders them, and runs
them against the specified database server. The database and all relative
objects are set up in one step.
So, I completely understand that I can write an SQL script that:
1 - creates a function that wraps SQL code that inserts static data into
dimension tables.
2 - executes that function
3 - destroys that function
But actually that is a bit weird, isn't it?
Thanks,
Daniel
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [SQL] Executing plpgsql scripts using psql, is that possible?
On Monday 16 January 2006 05:55 pm, Daniel CAUNE wrote:
> > -Message d'origine-
> > De : [EMAIL PROTECTED] [mailto:pgsql-sql-
> > [EMAIL PROTECTED] De la part de John DeSoi
> > Envoyé : lundi 16 janvier 2006 08:51
> > À : Daniel CAUNE
> > Cc : pgsql-sql@postgresql.org
> > Objet : Re: [SQL] Executing plpgsql scripts using psql, is that possible?
> >
> > On Jan 16, 2006, at 5:35 AM, Daniel CAUNE wrote:
> > > I would like to write some administration plpgsql scripts that
> > > populate some tables (dimension tables) and to execute them using
> > > psql. I’m not sure that is possible with psql as it is with Oracle
> > > sqlplus or SQL Server MSQuery:
> >
> > If you want to execute a plpgsql function from a file using psql,
> > just call it with SELECT. So your file might have:
> >
> > create or replace function my_function(params integer)
> > returns integer as $$
> > DECLARE
> >V_MyObjectID bigint;
> > BEGIN
> >V_MyObjectID := RegisterMyObject('a string', 'another string');
> >AddObjectProperty(V_MyObjectID, 'a string');
> >AddObjectProperty(V_MyObjectID, 'another string');
> >
> > END;
> > $$ language plpgsql;
> >
> >
> > SELECT my_function(1);
> >
> >
> > and then psql -f script.sql my_db
>
> Yes, but that requires creating a function while I would prefer not having
> do so, as I said in my previous mail: "I mean, without creating a function
> that wraps the whole, of course! :-)". Why? Actually this is not a
> function; this is a script that inserts static data into dimension tables
> such as Country, Language, etc.
>
> I have several scripts responsible for creating the database and all the
> objects (tables, views, constraints, indexes, user-defined functions, etc.)
> of my project. I would like to have some other scripts to initialize
> dimension tables, i.e. inserting static data in those tables. The idea is
> to automate the whole creation and initialization of a database on a
> PostgreSQL server; I already have an Ant task that searches for SQL files,
> orders them, and runs them against the specified database server. The
> database and all relative objects are set up in one step.
>
> So, I completely understand that I can write an SQL script that:
>
> 1 - creates a function that wraps SQL code that inserts static data into
> dimension tables. 2 - executes that function
> 3 - destroys that function
>
> But actually that is a bit weird, isn't it?
>
> Thanks,
>
>
> Daniel
>
>
> ---(end of broadcast)---
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>choose an index scan if your joining column's datatypes do not
>match
If you want to use plpgsql it will need to be within a function. In your reply
you mention creating user-defined functions as part of the set up procedure.
It would not be weird to include the static data function as part of that
procedure and then call it to load the data. I see no reason to destroy the
function after use. If that is not the route you want to take you may want to
look at the following for information on using COPY to load data from a file
into a table-
www.postgresql.org/docs/8.1/interactive/sql-copy.html
--
Adrian Klaver
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [SQL] Executing plpgsql scripts using psql, is that possible?
Daniel, On Jan 16, 2006, at 8:55 PM, Daniel CAUNE wrote: Yes, but that requires creating a function while I would prefer not having do so, as I said in my previous mail: "I mean, without creating a function that wraps the whole, of course! :-)". Why? Actually this is not a function; this is a script that inserts static data into dimension tables such as Country, Language, etc. Sorry I misunderstood the question. So, I completely understand that I can write an SQL script that: 1 - creates a function that wraps SQL code that inserts static data into dimension tables. 2 - executes that function 3 - destroys that function But actually that is a bit weird, isn't it? \copy is the easiest way in psql to populate tables. If you need more control, maybe copy the data to temp tables and then write plpgsql as needed to insert the data into the final tables. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Executing plpgsql scripts using psql, is that possible?
> If you want to use plpgsql it will need to be within a function. In your > reply > you mention creating user-defined functions as part of the set up > procedure. > It would not be weird to include the static data function as part of that > procedure and then call it to load the data. I see no reason to destroy > the > function after use. If that is not the route you want to take you may want > to > look at the following for information on using COPY to load data from a > file > into a table- > www.postgresql.org/docs/8.1/interactive/sql-copy.html > Yes, COPY may be an interesting option too. Thanks! ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] Characters that needs escape characters when inserting to database
Hi, Can anyone give me a list of characters that needs to be preceded by an escape character before inserting to database. Aside from characters listed below what are the other characters? "\" "'" Thanks I choose Polesoft Lockspam to fight spam, and you? http://www.polesoft.com/refer.html ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Characters that needs escape characters when inserting to database
On Jan 18, 2006, at 7:21 , Christian Paul B. Cosinas wrote: Can anyone give me a list of characters that needs to be preceded by an escape character before inserting to database. Take a look at this documentation on string constants. It should answer the questions you have. http://www.postgresql.org/docs/current/interactive/sql- syntax.html#SQL-SYNTAX-STRINGS Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
