*
I am banging my head over this. I want to select distinct values from a
varchar column and iterate through the values.
*
*I want to select the distinct values from this column and loop through
them (using as a variable) in a raise notice statement and also in an
update statement.
I have
I have a table with a varchar column.
I want to select the distinct values from this column and loop through
them (using as a variable) in a raise notice statement and also in an
update statement.
I have not been able to do this trying over 100 things in the last two
hours. I could not
,
and updating each row one by one.
Ideally would simply like one single update statement to do it all, but
not sure if there is a facility for this, or
how it could be done.
thanks for your help.
J.V.
On 4/28/2012 2:23 PM, David Johnston wrote:
On Apr 27, 2012, at 17:22, J.V.jvsr
starting with, so this might show
1
2
3
4
update table set new_column_id = (select id2 from join_table2 where
new_column_id=2);
but I do not want to write a loop and iterate through this stament
passing 1,2,3,4 to the above statement, just a single statement.
Is this possible?
thanks
J.v
to do this on three databases, I have figured out how to do this
in Oracle (build a string and the EXECUTE IMMEDIATE myString, but have
not figured out how to do this with PostgreSQL.
any ideas?
thanks
J.V.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make
that contains the create sequence
statement and execute that string, at least this is the way it is done
in Oracle.
I do not know how to use a variable in a create sequence statement in
PostgreSQL.
J.V.
On 4/27/2012 9:51 AM, Merlin Moncure wrote:
On Fri, Apr 27, 2012 at 10:37 AM, J.V.jvsr
I have created a stored function and wish to execute in pgadmin but keep
getting an error.
create or replace function myfunc() returns boolean
as $$
declare
begin
...
end;
$$ language plpgsql;
compiles and works just find, however when I run (in pgadmin III)
select myfunc();
I get errors
│ integer │
Foreign-key constraints:
a2_id_fkey FOREIGN KEY (id) REFERENCES a1(id)
Regards
Pavel Stehule
thanks
J.V.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
the common things around of
course, but uninstalling a single instance leaves a lot of droppings
around that need to be manually cleaned up.
J.V.
On 11/10/2011 8:18 PM, Craig Ringer wrote:
On 11/11/2011 06:18 AM, J.V. wrote:
yes, this is on windows.
Currently removing the data directory
it works.
thanks
J.V.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
data should be no problem, but when not sure about
exporting/importing binary data.
If there is a tool out there, or I should just dump out as csv and
import, let me know.
thanks
J.V.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription
of the above to a log file
Is there any tool internal to PostgreSQL that would allow me to do this?
I cannot monitor it from the code that is actually connecting sending
the query.
any help or pointers would be greatly appreciated.
J.V.
--
Sent via pgsql-general mailing list (pgsql-general
at 09:29:11AM -0700, J.V. wrote:
I am in need of a tool or method to see each/every SQL query that
hits the PostgreSQL database. By query I mean the query in SQL
syntax with all the parameters passed.
What I want to do is:
1) see the query
2) Determine how long the query takes to execute
What is a GUC and how do I use it?
On 11/17/2011 9:46 AM, Tomas Vondra wrote:
On 17 Listopad 2011, 17:32, hubert depesz lubaczewski wrote:
On Thu, Nov 17, 2011 at 09:29:11AM -0700, J.V. wrote:
I am in need of a tool or method to see each/every SQL query that
hits the PostgreSQL database
How do I drop a function that was created like so:
create or replace function process_table (action TEXT, v_table_name
varchar(100)) RETURNS BOOLEAN
AS $$
DECLARE
BEGIN
...
END;
$$ LANGUAGE plpgsql;
---
I have tried various ways, but it always fails.
J.V.
--
Sent via pgsql-general
this did not work.
On 11/15/2011 4:56 PM, Craig Ringer wrote:
On 11/16/2011 07:38 AM, J.V. wrote:
How do I drop a function that was created like so:
create or replace function process_table (action TEXT, v_table_name
varchar(100)) RETURNS BOOLEAN
AS $$
DECLARE
BEGIN
...
END;
$$ LANGUAGE
this does not work.
On 11/15/2011 4:56 PM, Rebecca Clarke wrote:
DROP FUNCTION process_table;
should work.
On Tue, Nov 15, 2011 at 11:38 PM, J.V. jvsr...@gmail.com
mailto:jvsr...@gmail.com wrote:
How do I drop a function that was created like so:
create or replace function
TEXT,
v_table_name varchar(100)) RETURNS BOOLEAN
AS $$
DECLARE
BEGIN
...
END;
$$ LANGUAGE plpgsql;
---
I have tried various ways, but it always fails.
J.V.
test=# begin;
create or replace function process_table (
action TEXT, v_table_name varchar(100)
) RETURNS BOOLEAN
I have a table with existing data for which I need to:
1) drop the single primary key column (int4)
2) recreate the column with the pk (not null) constraint deferred
3) repopulate the column from a sequence
4) enable the constraint
When I issue this command to add the column:
alter table
this fixed and go back and patch everything from 8.4
forwards so that it is fixed.
It is very time consuming for me to do 1..3 and I am sure others as well
and I want to get this fixed as soon as I can.
Regards,
J.V.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make
matching the pattern '*postgres*'
2. that has a directory value matching the pattern '*postgres*'
3. that has a key name matching the pattern '*postgres*'
thanks
J.V.
On 11/10/2011 2:36 PM, John R Pierce wrote:
On 11/10/11 1:30 PM, J.V. wrote:
Can anyone get me started on where
not point me to this doc:
http://developer.postgresql.org/pgdocs/postgres/hstore.html
What I am looking for is an example as listed in 1..3 above, and that is
not provided in the doc.
Regards,
J.V.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes
[mailto:pgsql-general-
ow...@postgresql.org] On Behalf Of J.V.
Sent: Tuesday, October 04, 2011 10:00 PM
To: pgsql-general
Subject: [GENERAL] how to disable all pkey/fkey constraints globally
Is there a generic way to drop just all primary key and foreign key constraints
on a given table?
I know
examples out there, it would be
greatly appreciated.
Sincerely,
J.V.
On 10/13/2011 2:18 AM, Sergey Konoplev wrote:
Probably this will help you
http://www.postgresql.org/docs/current/interactive/hstore.html
ps. Look at the each() function.
On 12 October 2011 22:45, J.V.jvsr...@gmail.com wrote
, but there is no clear
example or documentation. The key and the value would be text.
I want to iterate over the entire array (or list), so would need an
example on that or some pointers there, if there is help.
J.V.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make
I tried hstore with no luck.
How do I initialize the array?
How do I loop through each key/value pair in a stored function to
retrieve the key and value for each item in the list?
I need a list of key value pairs. Any options there?
J.V.
On 10/12/2011 8:51 AM, Merlin Moncure wrote
function.
that's all
any ideas?
J.V.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
value pairs. Any options there?
J.V.
On 10/12/2011 8:51 AM, Merlin Moncure wrote:
On Wed, Oct 12, 2011 at 8:46 AM, J.V.jvsr...@gmail.com wrote:
I want to create a variable that is an array or list of key/value pairs.
The key would be a table name and the value would be a sql statement
the table name and the
primary key field.
Regards,
J.V.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
, but given the table_name and the
constraint_name, how do I find the database column/field name associated
with that primary key?
J.V.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
pg_catalog table does not exist.
This is a solution for PostgreSQL 8.4.
If you know of a way I can get all primary key fields or have a query
that will work in 8.4, please help. I have done a lot of research and
cannot find a simple way.
J.V.
On 10/11/2011 3:29 PM, John R Pierce wrote
in the end, but want a stored
proc to simple reorder a primary key int column pulling from a named
sequence, if there is a single command.
thanks
J.V.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref
assuming the reverse could not be done because would have to
re-create each one individually?
Maybe I do not want to drop, so is there a way to simply disable all
globally (not drop) then enable all globally?
thanks
J.V.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org
to disable all existing constraints on all tables
everywhere (specifically all pkey fkey constraints irrespective of how
they were created) in one statement?
I have searched and searched and find nothing that works.
J.V.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org
(with the same
data) to be in the same table and to have it execute much more quickly
that the two statements currently do.
thanks
J.V.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Is there a way to disable all trigger user' in one statement? (and then
re-enable?)
One docs says primary keys and foreign keys are user triggers
thanks
J.V.
On 10/4/2011 1:12 PM, Guillaume Lelarge wrote:
Not sure where you look up, but there's no way to disable constraints in
PostgreSQL
the constraint).
J.V.
On 10/4/2011 1:09 PM, Scott Marlowe wrote:
On Tue, Oct 4, 2011 at 12:24 PM, J.V.jvsr...@gmail.com wrote:
Currently I can select one column into another with two statements:
alter tabletable_name add column id_old int;
updatetable_name set id_old = id;
Is there a way to do
Is it possible within a stored function to:
1) Access the file system (to rename a file for example)
2) Call a java program
thanks
J.V.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql
Yes, I know that one.
What I want to do however is within a stored procedure make a call and
store all tables in a given schema in a list.
Is this something you can answer?
thanks
J.V.
On 9/29/2011 3:25 AM, Richard Huxton wrote:
On 29/09/11 02:33, J.V. wrote:
Is is possible within
I need to run a series of stored procedures, what is the best way to
organize and run.
Ideally would like something like Oracle PL/SQL where I can put all
methods in one file and create a main() method
then just : select main() to have them all run.
If there is a way to do this, please let
What data types do I have access to in a stored proc?
I cannot seem to find the stored procedure manual.
I am not speaking of database field/column data types, but rather
stored proc data types.
Regards,
J.V.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make
would be needed.
thanks
J.V.
On 9/30/2011 2:45 AM, John R Pierce wrote:
create table yyy (id serial primary key, xxx_id int references xxx(id));
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql
need to extract this meta-data for a project.
Regards,
J.V.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
on this
does not show any examples.
Also where can I find a list of stored proc data structures (hash maps,
arrays, lists of arrays) or anything else that would be useful.
thanks
J.V.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http
In PostgreSQL (9.x), I have a variable that I want to format with commas.
I do not know how many digits will be in the number, it could be an int
(2Billion+) or a BigInt (18 digits or more).
All I simply want to do is to use: to_number( and have the commas put
in before display.
There are
45 matches
Mail list logo
