Tag Info

New answers tagged

1

You have hugely overcomplicated the solution. SQL code After removing much of the cruft, it boils down to this: DO $do$ DECLARE customer_schema text; BEGIN FOR customer_schema IN SELECT cname FROM customer LOOP EXECUTE format('COPY ( SELECT p.*, t1.*, t2.* -- etc. Or just: * FROM %1$I.portal p ...


0

what you are doing is maybe not the right approach. what you want is ... COPY (SELECT ...) TO 'some_file_name' CSV; the crux might be your loop. you can get rid around that one easy: there is a function called unnest, which can transform an array into a table which can then be joined or whatever (via a LATERAL join maybe). you are on the wrong path with ...


1

In plpgsql code (not in plain SQL!): EXECUTE format($$COPY (select * from %I.portal) TO '/tmp/out1.csv' WITH CSV$$, cname); cname being the variable holding a (yet unescaped) schema name. You need to understand: dollar quoting the format() function SQL injection and how to defend against it: SQL injection in Postgres functions vs prepared queries ...


1

Three misunderstandings: You cannot return data from a DO command. You cannot SELECT without target in plpgsql code. That's what the error message tells you. You don't need either for a simple SELECT statement. Just run the statement itself: abc() { $PG_CMD 'select * from customer' }


1

I wanted to release at least the direct PHP function (I'll eventually release the tools I'm building to something like Github) though I don't yet have the time to do much else other than post it here at least. Hopefully this will save others time and please feel free to suggest improvements. I'm not sure how secure the code is (it's merely a tool I'm only ...


2

General advice You mentioned it yourself, you just started using Postgres. Yet, you're tackling extremely advanced tasks right away, juggling system catalogs and operating with advanced dynamic SQL to automate things. While your objectives seem reasonable, you still need to start at the basics, like everyone else. There is just too much to explain here. ...


1

Any time you have a SQL statement that it dynamic in nature (can potentially change at runtime) that you need to run inside of a function, you need to run EXECUTE for it. See Section 40.5.4. Executing Dynamic Commands You need to be aware that executing SQL this way CAN be vulnerable to SQL Injection attacks. Your line above: SELECT setval('parts1_' || ...


1

To create a copy as close as possible you must use INCLUDING ALL with CREATE TABLE .. (LIKE ..) since there can be any number of columns with defaults that you obviously want to copy. You just want serial columns to get their own, independent sequence, which makes a lot of sense and probably should be the default behavior. For now, this should do the job: ...


1

The problem is that your parts1 table uses the sequence from the parts table (instead of its own sequence) ... thus it complains that you can't drop the parts table because the parts1 table depends on it (via default value for one of the columns)... You are getting this behavior in how you are creating the parts1 table... CREATE TABLE - see the LIKE ...


0

To summarize discussion on the topic, creating this sort of "Uber" view with several tables that have matching primary keys and similar (if not the same) row counts is not necessarily a bad idea for the purpose of streamlining code. Additionally, any tables or columns not used in subsequent queries on such a view are dropped from the execution plan in most ...



Top 50 recent answers are included