Postgresql – Select the Scheme and Table based on Select result in Postgresql

postgresqlselectsubquery

I have this query on postgree…

SELECT c.schemaname AS MySchema, a.relname AS MyTable, b.attname AS MyColumn    
FROM pg_class a 
INNER JOIN pg_attribute b ON (a.relfilenode = b.attrelid)
INNER JOIN pg_tables c ON (a.relname = c.tablename)
GROUP BY c.schemaname, a.relname, a.relfilenode, b.attname 
HAVING b.attname ILIKE('%PartNameColumn%');

The Result is:

MySchema, MyTable, MyColumn
Schema0   Table0   PartNameColumn0
Schema0   Table0   PartNameColumn1
Schema0   Table1   PartNameColumn0
Schema0   Table1   PartNameColumn1
Schema1   Table0   PartNameColumn0
Schema1   Table0   PartNameColumn1
Schema1   Table1   PartNameColumn0
Schema1   Table1   PartNameColumn1

Now I want to get some similar, but introducing a value:

SELECT * FROM MySchema.MyTable WHERE MySchema.MyTable.MyColumn = 'MyValue';

I don't know how pass the before select and create a new query according results IN ONLY ONE SENTENCE.

Please tell me how to do it in only one Query?

Thank you.

Best Answer

You can't. Columns can tell information that's useful but none of it will get you to a value without sending a query. So you'll have to have two queries,

Getting the column name,
Selecting rows where a specific column name is set to a value.

That second query requires planning. Some queries on column names don't require visiting the heap -- such as an index-only scan. Some are greatly sped up by visiting an index first, so you don't have to visit the entire heap and the values for the column are not stored on the column anyway, they're stored in a heap in PostgreSQL.

This may not be ideal, but it's what you've got to work with. See also

Related Solutions

Sql-server – Writing a query to find all employee salaries that are higher than their managers

By way of an example that addresses all the items @ypercube brought up in his comments on your question, you could redesign your table structure like:

CREATE TABLE Workers 
(
    WorkerID INT NOT NULL CONSTRAINT PK_Posts PRIMARY KEY IDENTITY(1,1)
    , ManagedByWorkerID INT NULL CONSTRAINT FK_Workers_WorkerID REFERENCES Workers(WorkerID)
    , Salary NUMERIC(10,2) 
);

To add some sample data for demo purposes:

INSERT INTO Workers VALUES (NULL, 1000000);
INSERT INTO Workers VALUES (NULL, 50000);
INSERT INTO Workers VALUES (1, 40000);
INSERT INTO Workers VALUES (1, 1000001);
INSERT INTO Workers VALUES (3, 40002);
INSERT INTO Workers VALUES (2, 29999);
INSERT INTO Workers VALUES (2, 60000);
INSERT INTO Workers VALUES (5, 60000);

And finally, the query to return workers who make more than their boss:

SELECT *
FROM dbo.Workers;

SELECT W.WorkerID
    , W.Salary
    , Bosses.WorkerID AS BossID
    , Bosses.Salary AS BossSalary
FROM dbo.Workers W
    INNER JOIN dbo.Workers Bosses ON W.ManagedByWorkerID = Bosses.WorkerID
        AND Bosses.Salary < W.Salary;

The results:

enter image description here

Postgresql – Getting around the constraint “column must appear in the GROUP BY clause or be used in an aggregate function”

seharusnya seperti ini(supposed to be like this):

SELECT person.name, person.id, license.expiry_date, COUNT(car) FROM person
  JOIN license ON license.person_id = person.id
  JOIN car ON car.owner_id = person.id
WHERE person.name = 'Charles Bannerman'
GROUP BY person.name, person.id, license.expiry_date, car.car;

Best Answer

Related Solutions

Sql-server – Writing a query to find all employee salaries that are higher than their managers

Postgresql – Getting around the constraint “column must appear in the GROUP BY clause or be used in an aggregate function”

Related Question