473,320 Members | 2,083 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

sql script confusion

I am in the process of testing PostGreSQL for our sales and product
database and I am a little confused about SQL scripting and various
available languages. (My background is mostly MSSQL Server).

I am using the PGAdmin tool and I would like to write a script to
update all sequences to the current max value in the table.

Do I need to use a specific language in a function to do this or does
it work as native SQL, as it would in T-SQL?

What I would like to do is something like -
(pseudo code)
declare cursor for select relname from pg_statio_user_sequences
open cursor
fetch next into var_relname
while not cursor.eof
set var_tblname=substring(var_relname, "0 until _")
set var_fieldname=substring(var_relname,"first _ until 2nd _")
select var_maxID=max(var_fieldname) from var_tblname
ALTER SEQUENCE var_relname
RESTART WITH var_maxID+1;
fetch next into var_relname
end loop

Can dynamic statements be written in "raw sql" or do they need to be
encompassed in a language? Do all language scripts have to be
functions or can I do something like:
Start Language Processing Here
....Code
End Language Processing
Thank You
Sim Zacks
IT Manager
CompuLab
04-829-0145 - Office
04-832-5251 - Fax
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #1
1 1190
Sim Zacks wrote:
Do I need to use a specific language in a function to do this or does
it work as native SQL, as it would in T-SQL?
You need to use a specific language.
What I would like to do is something like -
(pseudo code)
declare cursor for select relname from pg_statio_user_sequences
open cursor
fetch next into var_relname
while not cursor.eof
set var_tblname=substring(var_relname, "0 until _")
set var_fieldname=substring(var_relname,"first _ until 2nd _")
select var_maxID=max(var_fieldname) from var_tblname
ALTER SEQUENCE var_relname
RESTART WITH var_maxID+1;
fetch next into var_relname
end loop
For this plpgsql might be a good choice. Think sql with a few
loop/control structures and variables.
Can dynamic statements be written in "raw sql" or do they need to be
encompassed in a language? Do all language scripts have to be
functions or can I do something like:
Start Language Processing Here
...Code
End Language Processing


You need to create a function and then call it. You can create dynamic
SQL as a string and then EXECUTE it with plpgsql. The other languages
offer various ways too.

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: hupjack | last post by:
I finally joined the millions of cell phone users out there. I'm the 2nd phone on what is now a family share plan. (Our two cell phones use minutes from a central 400 minute peak time pool.)...
6
by: Mike Daniel | last post by:
I am attempting to use document.write(pageVar) that displays a new html page within a pop-up window and the popup is failing. Also note that pageVar is a complete HTML page containing other java...
12
by: Ali | last post by:
I have the following web page with a script in it. <html> <head> <title>Make Your Own Objects test</title> <script>
17
by: comp.lang.tcl | last post by:
The TCL command I am using will do a command-line action on a PHP script: set cannotRunPHP I have to do it this way as both the TCL script and the PHP script run as CLI. However, "info.php"...
17
by: CES | last post by:
All, I was wondering if their is a way of loading an external script fill from within a script?? <script language="javascript" type="text/javascript"> function test(var){ <script...
6
by: Jon Paal | last post by:
an excellent working solution for scrolling tables appears at this link http://www.litotes.demon.co.uk/example_scripts/tableScroll.html author has no contact page, but I have a question about...
5
by: Shuaib | last post by:
Hi! I have a python script which returns an Integer value. How do I call this script from a C programe, and use the result returned? Thanks for your time.
5
by: Ankur | last post by:
Hi Folks, I am new for this group. I want to clarify one thing what's a basic difference between Client Side Java Script and Server Side Java Script. how we can differentiate it. Why we called...
14
by: Tony | last post by:
I have a select with an onchange - it works find in Firefox, but in IE 6 I get an error: Object Expected. I have simplified things drastically, and still get the error. The HTML: <select...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.